Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
arpitkharkia
New Contributor II

Pivot Chart expression

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?

1 Solution

Accepted Solutions
MVP
MVP

Re: Pivot Chart expression

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

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

Capture.PNG


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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
9 Replies
MVP
MVP

Re: Pivot Chart expression

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
arpitkharkia
New Contributor II

Re: Pivot Chart expression

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!

MVP
MVP

Re: Pivot Chart expression

Try like:

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


Capture.JPG

fvelascog72
Valued Contributor

Re: Pivot Chart expression

Hi,

Try with:

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

Saludos

MVP
MVP

Re: Pivot Chart expression

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

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

Capture.PNG


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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
arpitkharkia
New Contributor II

Re: Pivot Chart expression

It wont work if i change my view

arpitkharkia
New Contributor II

Re: Pivot Chart expression

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?

arpitkharkia
New Contributor II

Re: Pivot Chart expression

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

MVP
MVP

Re: Pivot Chart expression

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Community Browser