Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ankit777
Valued Contributor

Pivot previous sales

Hi All

I have data as below

Year QtrSales
2016 q 111
2016 q 212
2016 q 313
2016 q 414
2017 q 115
2017 q 216
2017 q 317
2017 q 418
2018 q 119
2018 q 220
2018 q 321
2018 q 422

 

I have to show previous year same quarter data in pivot. 

desired output

 

Year QtrSalesPre Sales
2016 q 111 
2016 q 212 
2016 q 313 
2016 q 414 
2017 q 11511
2017 q 21612
2017 q 31713
2017 q 41814
2018 q 11915
2018 q 22016
2018 q 32117
2018 q 42218

 

please help.

Labels (1)
4 Replies

Re: Pivot previous sales

Try this

Table:
LOAD *,
	 Left([Year Qtr], 4) as Year,
	 Mid([Year Qtr], Index([Year Qtr], ' ') + 1) as Qtr;
LOAD * INLINE [
    Year Qtr, Sales
    2016 q 1, 11
    2016 q 2, 12
    2016 q 3, 13
    2016 q 4, 14
    2017 q 1, 15
    2017 q 2, 16
    2017 q 3, 17
    2017 q 4, 18
    2018 q 1, 19
    2018 q 2, 20
    2018 q 3, 21
    2018 q 4, 22
];

FinalTable:
LOAD *,
	 If(Qtr = Previous(Qtr), Peek('Sales')) as [Pre Sales]
Resident Table
Order By Qtr, Year;

DROP Table Table;
ankit777
Valued Contributor

Re: Pivot previous sales

Thanks for your response. 

But by any chance can we do it in front end?

Re: Pivot previous sales

What is your expression for Sales? Is it just Sum(Sales)? Also, do you have a only Quarter field in your app?

ankit777
Valued Contributor

Re: Pivot previous sales

No. Quarter is my secondary dimension. I have one more dimension. Primary dimension is in rows while secondary is in column.