Optional parameters in table calculation functions

[quicksightadmin]

I’m trying to use the periodOverPeriodPercentDifference function. I know it takes the parameters
(measure, date, period (optional), offset(optional))

What I want to do is run it with default period but custom offset. So I get the period to default to granularity of the visual aggregation but offset 2 periods back instead of 1 for example.

So I tried all of these different options to see if I could make it work but it tells me it isn’t correct. Is there a way to achieve this?
periodOverPeriodPercentDifference(sum({sales}),{sale_date},2) ← Leave it out
periodOverPeriodPercentDifference(sum({sales}),{sale_date},[],2) ← Pass empty list
periodOverPeriodPercentDifference(sum({sales}),{sale_date}, ,2) ← Pass empty string
periodOverPeriodPercentDifference(sum({sales}),{sale_date}, DEFAULT,2) ← Pass the string “DEFAULT”

[robdhondt]

Hi @quicksightadmin -

Here is a way you can solve this. One limitation is that it will not show values for subtotals/totals.

Step 1 - Create a string parameter/control with your date grain options

Step 2 - Create a parameter for your offset

Step 3 - Create a calculated field that switches your date grain parameter to a truncated date. This will be used for sorting.
c_date_grain_dt

max(ifelse(${pGrain}='Week',truncDate('WK', {Order Date}),
${pGrain}='Month',truncDate('MM', {Order Date}),
${pGrain}='Year',truncDate('YYYY', {Order Date}),
null
))

Step 4 - Create a calculated field that switches your date grain parameter to a string date. This will be used for the visual.

ifelse(${pGrain}='Week',concat('WK', tostring(dateDiff(truncDate("WK", truncDate('YYYY', {Order Date})), {Order Date}, 'WK')+1)," ",toString(extract('YYYY', {Order Date}))),
${pGrain}='Month',concat(left(formatDate({Order Date}, 'MMM/dd/yyyy'), 3)," ",toString(extract('YYYY', {Order Date}))),
${pGrain}='Year',toString(extract('YYYY', {Order Date})),
null
)

Step 5 - Add a calculated field to make sure the data is not spare (missing date periods).
c_grain_datediff

ifelse(${pGrain}='WEEK',dateDiff({c_date_grain_dt}, lag({c_date_grain_dt},[{c_date_grain_dt} ASC], ${pOffset}), 'WK'),
${pGrain}='MONTH',dateDiff({c_date_grain_dt}, lag({c_date_grain_dt},[{c_date_grain_dt} ASC], ${pOffset}), 'MM'),
${pGrain}='YEAR',dateDiff({c_date_grain_dt}, lag({c_date_grain_dt},[{c_date_grain_dt} ASC], ${pOffset}), 'YYYY'),
null
)

Step 6 - Use the lag function to create calculated field using your grain and offset parameters. Do not include if there is a gap in dates.

ifelse({c_grain_datediff}=-1,lag(sum(Sales),[{c_date_grain_dt} ASC], ${pOffset}),null)

Step 7 - Create a percent difference calculation using your lag calculation
c_previous_period_percentchange

(sum(Sales)-{c_lag_previous_period})/{c_lag_previous_period}

Step 8 - Sort your visual by c_date_grain_dt

Result: