Dynamic Time Period in QS Calculated Fields

Question & Answer
, , ,
1

Hello!

I have data with a column indicating period_type (day, week, month and quarter) and a measure field. Based on whether I select Day, Week, Month or Year from a dropdown, I would like the PoP % difference to use these dynamic time periods instead of hardcoded to use either of these.

Thank you!

2

Hi @Madura_Puri , welcome back to the Community :slight_smile:

You can obtain this, if I understood correctly the issue, by generating different calculated fields based on you Control+Parameter selection.

Let’s say you Parameter, modified when you change the Control dropdown values, is called TimeGranularity. Let’s say also you have a field called MyDate.

You can create calculated fields like this (there are 4 different calculated fields):

PoP_Day = periodOverPeriodDifference(sum(Sales), {MyDate}, "DAY", 1)
PoP_Week = periodOverPeriodDifference(sum(Sales), {MyDate}, "WEEK", 1)
PoP_Month = periodOverPeriodDifference(sum(Sales), {MyDate}, "MONTH", 1)
PoP_Year = periodOverPeriodDifference(sum(Sales), {MyDate}, "YEAR", 1)

Then you can create a final field to be used in your visual, let’s call it PoP:

ifelse(
  ${TimeGranularity} = 'Day', {PoP_Day},
  ${TimeGranularity} = 'Week', {PoP_Week},
  ${TimeGranularity} = 'Month', {PoP_Month},
  ${TimeGranularity} = 'Year', {PoP_Year},
  NULL
)

Now you just have to select this field in your visual, and this should change when you select a different value in the control!

Let me know if this helps :slight_smile:

Andrea