Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
arpitkharkia
Creator III
Creator III

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
jonathandienst
Partner - Champion III
Partner - Champion III

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

View solution in original post

9 Replies
jonathandienst
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

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!

tresesco
MVP
MVP

Try like:

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


Capture.JPG

fvelascog72
Partner - Specialist
Partner - Specialist

Hi,

Try with:

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

Saludos

jonathandienst
Partner - Champion III
Partner - Champion III

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
Creator III
Creator III
Author

It wont work if i change my view

arpitkharkia
Creator III
Creator III
Author

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
Creator III
Creator III
Author

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

jonathandienst
Partner - Champion III
Partner - Champion III

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