Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

It should be :

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

Highlighted
Partner
Partner

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))

Highlighted
Creator II
Creator II

Please check the sample app

Highlighted
Partner
Partner

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

Highlighted
Creator III
Creator III

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

Highlighted
Creator III
Creator III

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.

Highlighted
Partner
Partner

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:

Highlighted
Creator III
Creator III

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

Highlighted
Partner
Partner

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

Capture.PNG