topic Re: specific pivot feature in QlikView

specific pivot feature

Anonymous — Wed, 18 Dec 2013 10:54:02 GMT

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):

thanx

Re: specific pivot feature

luciancotea — Wed, 18 Dec 2013 11:04:30 GMT

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...

Re: specific pivot feature

Anonymous — Thu, 19 Dec 2013 07:52:48 GMT

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?

Re: Re: specific pivot feature

luciancotea — Thu, 19 Dec 2013 08:21:53 GMT

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:

	2012	2012	2012	2011	2011	2011
Store	Sales	Diff	Diff [%]	Sales	Diff	Diff [%]
Store 1
Store 2
Store 3
Store 4

Re: specific pivot feature

jvishnuram — Thu, 19 Dec 2013 08:54:42 GMT

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%')

Re: specific pivot feature

Anonymous — Thu, 19 Dec 2013 09:49:10 GMT

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

Re: specific pivot feature

Anonymous — Tue, 07 Jan 2014 09:11:57 GMT

UP! (???)

Re: specific pivot feature

Tue, 07 Jan 2014 09:44:16 GMT

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

Re: specific pivot feature

Anonymous — Tue, 07 Jan 2014 11:17:36 GMT

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

Re: Re: specific pivot feature

Tue, 07 Jan 2014 11:27:10 GMT

please find below the application with dynamic set analysis

regards

stephane

Re: specific pivot feature

Anonymous — Wed, 08 Jan 2014 08:18:49 GMT

that's it! do you think it could be possible to set it iterative? i.e.:

let's assume today is 7/1/2022 (the 7th of January year 2022);
the date of the first record is 4/5/2011 (the 4th of May 2011)
we have 2022-2011+1 = 12 years passing through
instead of writing sum({<year = {$(=max(year)-2)}>}sales) ... could Qlik manage something iterative to create expressions automatically for each year recorded?.

thanx, bye