Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
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.