topic Re: Pivot Chart expression in QlikView

Pivot Chart expression

arpitkharkia — Mon, 29 Jan 2018 10:30:16 GMT

Hi everyone,

I have a chart like

Currently my coll% is showing coll/Setup but what i want is that the coll amount should always be divided by the first setup only, i.e 40/100,20/100,10/100 giving me 40%,20%,10%. This should be respective of each and every month. Basically i want to take the setup amount where my setup month is same as my collection month.

Is there a way to this?

Re: Pivot Chart expression

jonathandienst — Mon, 29 Jan 2018 10:38:10 GMT

You may want to create a derived flag field in the load script to indicate the correct setup field. If you need more specific help, I suggest that you upload a small sample qvw with some representative sample data.

Re: Pivot Chart expression

arpitkharkia — Mon, 29 Jan 2018 10:43:58 GMT

Hi,

Please find the attachments above. Let me know if you need anything else. Yes, i have created a flag but im not sure how to use it.

Thanks!

Re: Pivot Chart expression

tresB — Mon, 29 Jan 2018 10:50:05 GMT

Try like:

sum(CollectionAmount)/Above(sum(SetupAmount), RowNo()-1,0)

Re: Pivot Chart expression

fvelascog72 — Mon, 29 Jan 2018 10:51:46 GMT

Hi,

Try with:

Sum(CollectionAmount)/Max(TOTAL <SetupMonth> {<flag = 1>}SetupAmount)

Saludos

Re: Pivot Chart expression

jonathandienst — Mon, 29 Jan 2018 10:52:45 GMT

I think this is the Coll% expression you are looking for

sum(CollectionAmount)/sum({<flag = {1}>} total <SetupMonth> SetupAmount)

Note the flag for SetupMonth = Nov is set for CollectionMonth = Nov (90). perhaps the flag should be Jan (110). The expression is interpreting the value correctly according to the flag.

Re: Pivot Chart expression

arpitkharkia — Mon, 29 Jan 2018 10:55:01 GMT

It wont work if i change my view

Re: Pivot Chart expression

arpitkharkia — Mon, 29 Jan 2018 11:17:17 GMT

Its working just fine. Dont worry about the Nov to Jan issue i will handle that. One more small help. I want all those setup amount with flag 1 to be shown beside the respective months as dimension so as to make it easy for the users to read the data. Can you please help me with that?

Re: Pivot Chart expression

arpitkharkia — Mon, 29 Jan 2018 11:18:48 GMT

Its just that i dont want to confuse the users with multiple setup amounts

Re: Pivot Chart expression

jonathandienst — Tue, 30 Jan 2018 05:15:47 GMT

Just use the denominator of the expression -

sum({<flag = {1}>} total <SetupMonth> SetupAmount)

I suggest that you do it as an expression rather than a dimension, so you can avoid the aggr(). Drag it into the desired position in the pivot.