I have a dashboard with daily/weekly/monthly/quarterly granularity according to the filter choosed by user. Now I want to add DoD, WoW, MoM, QoQ columns in between each column to reflect each metric’s change from yesterday. How can I do it?
Hi @Bonnie213 , welcome to the QS Community!
How are you changing the values aggregation in this moment?
I’ve tried replicating your case.
Let’s say I have these fields for grouping:
- Region
- Subregion
- Country
Then for value and ordering I have
- Order_Date
- Sales
I’ve created a param, e.g. TimeGranularity, and then a control which can have as values Day, Week, Month or Year.
Now I have one calculated field to get the correctly aggregated date, something like OrderDate_Trunc:
ifelse(
${TimeGranularity} = 'Day', truncDate('DD', {Order Date}),
${TimeGranularity} = 'Week', truncDate('WK', {Order Date}),
${TimeGranularity} = 'Month', truncDate('MM', {Order Date}),
${TimeGranularity} = 'Year', truncDate('YYYY', {Order Date}),
{Order Date}
)
Now I can use this field as column in my Pivot Table.
To obtain the period over period difference (e.g.) I can create different fields, like (each row is a different field):
PoP_Day = periodOverPeriodDifference(sum(Sales), {OrderDate_Trunc}, "DAY", 1)
PoP_Week = periodOverPeriodDifference(sum(Sales), {OrderDate_Trunc}, "WEEK", 1)
PoP_Month = periodOverPeriodDifference(sum(Sales), {OrderDate_Trunc}, "MONTH", 1)
PoP_Year = periodOverPeriodDifference(sum(Sales), {OrderDate_Trunc}, "YEAR", 1)
Then a final field to select what has to be shown in the Pivot, like PoP:
ifelse(
${TimeGranularity} = 'Day', {PoP_Day},
${TimeGranularity} = 'Week', {PoP_Week},
${TimeGranularity} = 'Month', {PoP_Month},
${TimeGranularity} = 'Year', {PoP_Year},
NULL
)
This last field become our new value in the Pivot, and now by changing the Time Granularity, everything should change accordingly.
Let me know if this works for you! 
Andrea
Thank you so much! That perfectly solved my question!
1 Like