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: 
baarathi
Creator III
Creator III

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

It should be :

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

siddharth_s3
Partner - Creator II
Partner - Creator II

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

Please check the sample app

OmarBenSalem

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

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

baarathi
Creator III
Creator III
Author

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

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

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

OmarBenSalem

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

Capture.PNG