topic Re: Displaying Change in a Pivot Table in QlikView

Displaying Change in a Pivot Table

Wed, 02 Apr 2014 06:57:27 GMT

PFA the qlikview file.

here I want a column called "Change" as follows. Change is basically calculated as Mar2013 -minus June 2013, Sep2013 minus June2013 and so on. Any help would be great.

Mar2013 Change June2013 Change Sep2013 Change Feb2014

600 900 1500 500 2000 -1000 1000

Thanks,

Ram

Re: Displaying Change in a Pivot Table

its_anandrjs — Wed, 02 Apr 2014 07:11:49 GMT

Any sequence of this month values

Re: Displaying Change in a Pivot Table

its_anandrjs — Wed, 02 Apr 2014 07:14:49 GMT

Hi check below script add another column PickCount for pick above value

load * inline

[

PeriodCode, Count,

Feb2014 ,1000

Sep2013 ,2000

Jun2013 ,1500

Mar2013 ,600

];

LOAD

Peek(Count) as PickCount

Resident A;

DROP Table A;

and in pivot table

Dimension : PeriodCode

Expression1 : Sum(Count)

Expression2 : sum(PickCount) - sum(Count)

Hope this helps

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 07:18:20 GMT

No. I need a solution in the front end.

Sent from my iPhone

Re: Displaying Change in a Pivot Table

its_anandrjs — Wed, 02 Apr 2014 07:26:34 GMT

In straight table you can do like

Dim :- PeriodCode

Expression1 :- Sum(Count)

Expression2 :- Sum(Count) -Above(sum(Count))

Its is necessary that you use only Pivot table let me know.

Hope this helps

Re: Displaying Change in a Pivot Table

mangalsk — Wed, 02 Apr 2014 07:39:00 GMT

Hello ,

PFA

It gives following output:

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 08:54:03 GMT

cannot afford do that in a script mate. Thanks anyway.

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 08:54:38 GMT

thanks Anand. Yes, this has to be a Pivot table unfortunately.

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 09:04:05 GMT

have you tried previous() ? i only used it in the script, but it might work in frontend expressions too.

f.e. in script

if(previous(item_ID)=[item_ID],

DATA-previous(DATA),-1) as DATA_DIFF,

might work as

if(previous(item_ID)=[item_ID],

DATA-previous(DATA),-1)

or even as

DATA-previous(DATA)

but these kind of functions work better in the script - more performant anyway

Re: Re: Displaying Change in a Pivot Table

tresB — Wed, 02 Apr 2014 09:06:06 GMT

Doing it in the front-end would be a cumbersome work and a kind of static report (not recommended). Using straight table, create one expression for each monthyear like in attached sample.

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 09:11:37 GMT

tresesco, this is how it is now, a straight table with individual expressions. Once they load a data for more periods in the future, we have to go and modify the chart. which i think is not advisable. That is why i am looking for a viable solution. I basically want to pivot the period so that they appear in the column and be more dynamic. only think i am unable to achieve is the difference.

Re: Re: Displaying Change in a Pivot Table

tresB — Wed, 02 Apr 2014 09:22:24 GMT

See attached a better option.

Re: Displaying Change in a Pivot Table

mangalsk — Wed, 02 Apr 2014 09:24:21 GMT

Hello ,

For expression level use following:

=fabs(sum(Count)- Above(sum((Count))))

Re: Displaying Change in a Pivot Table

Wed, 02 Apr 2014 09:26:43 GMT

No Mangal. Doesn't work in a Pivot table.

Re: Displaying Change in a Pivot Table

Anonymous — Wed, 02 Apr 2014 09:41:36 GMT

Hi,

Please find the attached file. Is it this what you what?

Thanks and Regards,

Jerry