Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

baarathi
Contributor II

Set Expression

Hi I have attached a file in which i need to calculate the percentage for Revenue % = (sum(Amount) - sum(Adjustment)) / Total (sum(Amount) - sum(Adjustment))  for particular month-year.

12 Replies
OmarBenSalem
Esteemed Contributor

Re: Set Expression

It should be :

(sum(Amount) - sum(Adjustment)) / (sum(Total {<YourMonthField>} Amount) - sum(Total {<YourMonthField>} Adjustment))

siddharth_s3
Contributor

Re: Set Expression

I fail to understand why you need set analysis here?
Use the keyword "All" before sum(amount) in denominator if you don't want denominator to change.

Like

= (sum(Amount) - sum(Adjustment)) / (sum(ALL Amount) - sum(ALL Adjustment))

qlik4asif
Contributor II

Re: Set Expression

Please check the sample app

OmarBenSalem
Esteemed Contributor

Re: Set Expression

ALL is NOT the right way to do it, u should be using Total {<YourDimension(used in the chart)>}

to be able to track the real percentages if u make a selection on other dimensions

baarathi
Contributor II

Re: Set Expression

Sir, But I am getting error in set modifier expression while using this

baarathi
Contributor II

Re: Set Expression

This Expression works partially. That is when i select a particular year and particular month, it shows the percentage out of hundred, whereas if filter is not selected, it shows percentage for all the years.

OmarBenSalem
Esteemed Contributor

Re: Set Expression

Is this correct?

the measure is :

=(Sum(Amount) - Sum(Adjustment)) / (Sum(total {<"Year", "Month">} Amount) - Sum(total {<"Year", "Month">} Adjustment))

Capture.PNG

even , when u select a Year, the percentage doesn't become 100%:

Capture.PNG

But would return each % based on the selection of the Type:

Capture.PNG

Now if u want to it to be based on the Type from the start :

u change the expression as follow:

=(Sum(Amount) - Sum(Adjustment)) / (Sum(total <PType> Amount) - Sum(total <PType> Adjustment))

result:

Capture.PNG

see the attached app:

baarathi
Contributor II

Re: Set Expression

omarbensalem‌ sir I want pivot chart output similar to Desired Output.xls, where as I am getting output similar to Undesired Output.xls

OmarBenSalem
Esteemed Contributor

Re: Set Expression

But u don't have values in both low and hight for each year:

Capture.PNG

Community Browser