periodOverPeriodDifference for workdays

Question & Answer
1

Hi,

How can I use the periodOverPeriodDiffernce function for workdays? I am calculating the number of tickets difference between days and I am considering only workdays. If I use ‘Day’ then the difference between Friday and Monday is null.

periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,1)

Thanks in advance
Srilatha

2

You can check if it’s Monday and then if it is, subtract 3 days from it.

ifelse(extract(‘{Range_Date}’,‘WD’)=2,
periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,3),periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,1))

Let me know if that works for you.

3

Hi

Thank you for the logic. But I am getting error when I tried the below formula

ifelse (extract(‘WD’, {Range_Date})=2,
periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,3),
periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,1))

Error message “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.”
at
periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,3),

Can you please help me understand what I have missed here.

4

Hi @Srilatha_P,

Thanks for your question and following the QuickSight Learning Series.

I was struggling with this too, but may have an alternative solution.

I create a calculated field “DayType” as follows to determine if the day is a WeekDay or Weekend
ifelse(extract('WD',{Order Date})=1 OR extract('WD',{Order Date})=7,"Weekend","Weekday")

Then I add a filter to the visual to include only DayType=“Weekday”.

Next I created a calculated field “popDifference” as follows:
difference( max(Sales), [{Order Date} ASC], -1 )

I ran some tests to validate and it appears to work for my data, but please let me know if this works for you.

Regards,
Andrew