Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to compare % difference in value between Quarter and Quarter PY, e.g. 2018 Q1 and 2017 Q1. The issue arises where quarters are incomplete - assuming for 2018 Q1 we only have data for January and February, the below formula compares the sum of Jan and Feb 2018 to Jan, Feb and Mar 2017 when I'd like it to compare Jan + Feb 2018 to Jan + Feb 2017 when quarter data is incomplete. How should I adjust the formula to take that into consideration?
100-Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgoPY)}>}[kpi_value])/
Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgo)}> }[kpi_value])*100)
Thanks very much in advance!
Hi, Laura
please see my solution by adding a new field in your data load script
DayNumberOfQuarter(%date_key) as DayNumberOfQuarter
And then add the condition to your set analysis where the day number of the quarter PY is less than the day number of quarter as of today
100-Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgoPY)},DayNumberOfQuarter={'<=$(=DayNumberOfQuarter(Today()))'}>}[kpi_value])/
Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgo)}> }[kpi_value])*100)
Hope this helps
ZZ
Hi, Laura
please see my solution by adding a new field in your data load script
DayNumberOfQuarter(%date_key) as DayNumberOfQuarter
And then add the condition to your set analysis where the day number of the quarter PY is less than the day number of quarter as of today
100-Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgoPY)},DayNumberOfQuarter={'<=$(=DayNumberOfQuarter(Today()))'}>}[kpi_value])/
Sum({$<[%date_key.autoCalendar.QuartersAgo]={$(vMinQuartersAgo)}> }[kpi_value])*100)
Hope this helps
ZZ
Thanks very much, that worked!