Dynamically changing Period over Period calculation

Question & Answer
, , ,
1

I have calculated field to calculate the WoW difference. My Dashboard has a control to change the aggregation from Daily/Weekly/Monthly. However, the WoW doesn’t change to DoD or MoM
periodOverPeriodDifference({Metric value}
,{time frame group(date)}
,'WEEK'
,1)

I tried the below but threw an error saying expected is a period
periodOverPeriodDifference({Metric value}
,{time frame group(date)}
ifelse(
${TimeFrame} = 'Monthly' , MONTH,
${TimeFrame} = 'Weekly' , WEEK,
${TimeFrame} = 'Daily',DAY,
WEEK
)
,
)
Is there a way to pass period from string

1 Like
2

Hello @resjames, welcome to the QuickSight community!

I think if you changed the logic to have the periodOverPeriod functions returned as the result of the ifelse statement rather than trying to change the period value in the calculation, you may have more success. I’ll put an example below:

ifelse(
${TimeFrame} = 'Weekly',
periodOverPeriodDifference(sum({Metric value}), {time frame group(date)}, WEEK, 1),
${TimeFrame} = 'Daily',
periodOverPeriodDifference(sum({Metric value}), {time frame group(date)}, DAY, 1),
periodOverPeriodDifference(sum({Metric value}), {time frame group(date)}, MONTH, 1)
)

This would check for the period selected by the user and return the calculated field with the proper aggregation. I’ll mark this as the solution, but let me know if you have any remaining questions. Thank you!