I have created a calculated field “DynamicDimension” as follows:
ifelse(
${Dimension} = ‘day’, truncDate(“DD”, {ride_current_start_time}),
${Dimension} = ‘week’, truncDate(“WK”, {ride_current_start_time}),
${Dimension}= ‘month’, truncDate(“MM”, {ride_current_start_time}),
${Dimension} = ‘quarter’, truncDate(“Q”, {ride_current_start_time}),
${Dimension} = ‘year’, truncDate(“YYYY”, {ride_current_start_time}),
truncDate(“DD”,{ride_current_start_time})
)
I added this field into my report and also created a parameter with the selection value of day,week,month,quarter,and year. I added my dynamicdimension field to my rows column in my table, and also added a calculated field to my values field well. The calculated field leverages my dynmicdimension field as follows: periodOverPeriodPercentDifference((distinct_countIf({ride_id}, {was_ride_completed} = ‘Yes’)), DynamicDimension)
When i select anything other than day in my control, the report will not show me the periodoverperiodpercentdifference values. This only works for the day level aggregation. How can i edit my report so the values will show for other aggregation levels? Thank you in advance to any help provided, and if any further context is needed, please ask and i will screen shot anything needed!
you need to use the “free-form” layout. Select the level of aggregation that you need for each select option in the control ( day, week, quarter, year) in a different visual, it means you have to build 4 visuals. Each one for each level of aggregation, and containing as only needed column the one from:
periodOverPeriodPercentDifference((distinct_countIf({ride_id}, {was_ride_completed} = ‘Yes’)), DynamicDimension)
For each visual, you will need to use conditional rule (from visual settings). Hide by default all visuals except one (the one that will be visible by default). For each visual, map its visibility rule to the corresponding selection from the control to make it visible:
Thank you for this solution! This works and i also found a second solution by leveraging the percentdifference formula instead of the periodoverperiodpercent formula.
Thank you,
If you dont mind, in order to benefit others in the community, please post and answer with a screenshot with your alternative solution and the formula you used.
Thanks,
*Please click “like” if you found my answer useful
Hello @JoseB-aws,
In this solution, the hidden visuals are whitespace in dashboard. How can I show my visual so that it is left-justfied? e.g. If i have daily, monthly, quarterly visuals, then when I select quarterly option, i want to show it on left most space in dashboard. In short, each visual (daily, monthly, quarterly, yearly) should be shown at same coordinates.
Please let me know. Thanks,