Date aggregation for end user via parameter control

I have created a calculated field “DynamicDimension” as follows:
${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}),
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!

Day aggregation

Week aggregation

This is a little bit tricky.
Since the aggregation is done at view level (in the field wells, select the aggregation level),

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:

(in this example, when my control = Q, the visual with the aggregation level set to Quarter will be the only one visible).

1 Like

Thank you for this solution! This works and i also found a second solution by leveraging the percentdifference formula instead of the periodoverperiodpercent formula.

1 Like

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.

*Please click “like” if you found my answer useful

1 Like

I was looking for a solution to this and I found it here, thank you very much for the help @JoseB-aws.

1 Like