Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

frankiegoes2hol
New Contributor III

specific pivot feature

hello everybody.

i have this data set: shops and their sales per year. i'd like to create a pivot chart showing the sales per shop/year AND the differences (absolute and percent) between consecutive years. see the followng image: image (Excel example):

Qlik_PVT.JPG.jpg

thanx

10 Replies
luciancotea
Valued Contributor

Re: specific pivot feature

2012             =SUM( {<Year={2012}> Sales}

2012-2011     =SUM( {<Year={2012}> Sales} - SUM( {<Year={2011}> Sales}

2012-2011%  =NUM( SUM( {<Year={2012}> Sales} / SUM( {<Year={2011}> Sales} - 1, '#,##0%')

.

.

.

you get the picture...

frankiegoes2hol
New Contributor III

Re: specific pivot feature

i shouldn't insert an expression for each year, it's meant to be dynamic and automatically do all the calculations for each year found in the data set. is it possible?

luciancotea
Valued Contributor

Re: Re: specific pivot feature

Yes, it's possible to do that. It will not look exactly like that, but I don't think it's an issue.

Add "Year" as dimension (drag it horizontally after) and create 3 expressions: Sales, Diff and Diff [%]

Sales: =SUM( Sales )

Diff: = this takes some time and I don't have it right now

Diff [%]:

You will get:

201220122012201120112011
StoreSales Diff Diff [%]Sales Diff Diff [%]
Store 1
Store 2
Store 3
Store 4
jvishnuram
Contributor III

Re: specific pivot feature

Hi Car,

The output you are looking for is quite possible and also it can be achievable as a dynamic one.

2012 (=Max(Year))            =SUM( {<Year={"=Max(Year)"}> Sales}

2012-2011 (= (Max(Year)-(Max(Year)-1) ) )     =SUM( {<Year={"=Max(Year)"}> Sales} - SUM( {<Year={"=Max(Year)-1"}> Sales}

2012-2011% (=(Max(Year)-(Max(Year)-1) ))  % =NUM( SUM( {<Year={"=Max(Year)"}> Sales} / SUM( {<Year={"=Max(Year)-1"}> Sales} - 1, '#,##0%')

frankiegoes2hol
New Contributor III

Re: specific pivot feature

my Year dimension already is in a numeric format: is the syntax of the expressions you suggest ready for that?

frankiegoes2hol
New Contributor III

Re: specific pivot feature

UP! (???)

Not applicable

Re: specific pivot feature

Please find below an application that try to solve your problem.

Sales columns have to be expression as you can not use your year dimension in the pivot table.

Please tell me if it is usefull or not.

Regards

Stéphane

frankiegoes2hol
New Contributor III

Re: specific pivot feature

very useful. now i just should get it dynamic, inserting each year from earliest to latest automatically in the pivot chart

Not applicable

Re: Re: specific pivot feature

please find below the application with dynamic set analysis

regards

stephane

Community Browser