Skip to main content

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
JustinDallas
Specialist III
Specialist III

YOY Sales in Pivot Table

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.

Labels (4)
0 Replies