Hello Folks,
I have a pivot table, and I'd like to show % of Change based on the previous year. Below is my dummy data.
DummySales:
Load *,
DayStart(Floor(Num([Sales Date]))) AS %fact_calendar_key,
;
Load *,
Date#(SalesDateTxt, 'YYYY-MM-DD') As 'Sales Date'
;
Load * Inline
[
'Product', 'Amount', 'SalesDateTxt'
'Car Jacks', 5000, '2018-10-10'
'Car Jacks', 1200, '2017-10-11'
'Car Jacks', 2500, '2016-10-09'
'SUV Jacks', 400, '2018-09-10'
'SUV Jacks', 1200, '2017-09-11'
'SUV Jacks', 300, '2016-09-09'
]
;
Drop Field SalesDateTxt;
MasterCalendar:
Left Keep(DummySales)
LOAD
DayStart(Floor(Num(TempDate))) AS %fact_calendar_key,
Date(DayStart(TempDate)) AS CalDate,
Dual(Year(TempDate), Year(TempDate)) AS Year,
;
//=== Generate a temp table of dates ===
LOAD
DATE(mindate + IterNo()) AS TempDate,
mindate,
maxdate // Used in InYearToDate() above, but not kept
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
LOAD
MIN(FieldValue('%fact_calendar_key', recno()))-1 as mindate,
MAX(FieldValue('%fact_calendar_key', recno())) as maxdate
AUTOGENERATE FieldValueCount('%fact_calendar_key');
EXIT Script
;
What I am trying to achieve is something like the following:
| Year |
2016 |
2017 |
2018 |
| Product |
| Car Jacks |
108.3333 |
-316.667 |
- |
| SUV Jacks |
-75 |
66.66667 |
- |
What I've tried is
Sum({<Year={$(=Max(Year)-1)}>}Amount)
But that doesn't work, because it's looking at the Max of the selectable years, not the value of the current year dimension.
I would like to use Set Analysis if possible, and avoid using Above or other charting keywords.
Any help is greatly appreciated.
