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.

QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
Showing results for 
Search instead for 
Did you mean: 
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.

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;

Left Keep(DummySales)
 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 === 
 DATE(mindate + IterNo()) AS TempDate,
 maxdate // Used in InYearToDate() above, but not kept 
WHILE mindate + IterNo() <= maxdate;
//=== Get min/max dates from Field ===/
 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
Car Jacks 108.3333 -316.667 -
SUV Jacks -75 66.66667 -


What I've tried is 



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