Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Using set analysis to calculate percentage

Greetings,

I am using the following dimension fields in a straight chart: project number, project name, allocation code, project type. I have expressions rowno() ,SUM(Amount) for 2011, 2012, 2013, and Year To Date totals. I need to create a percentage. There is an extra field, "business unit", required to create this percentage, but it is not used in the dimensions. The formula for the percentage should be " project total by business unit divided by the project total". I have tried using expressions like sum({$<business unit = business unit>}Amount)/SUM({$<project=project>}Amount) which gives me 0s down the board. I have tried aggr(SUM({$}amount),businessunit)/aggr(SUM({$}amount),project) * .01. This produces dahes "-" down the board. I am at a standstill. I am sure somebody has done this before but I am not choosing the correct search criteria for this site. Any assistance is greatly appreciated.

Thanks,

Frank

1 Solution

Accepted Solutions
shane_spencer
Specialist
Specialist

Please take a look at the pivot table and straight table I have created as examples on Sheet1. I think this is what you are after, if not please elaborate.

View solution in original post

5 Replies
Gysbert_Wassenaar

The formula for the percentage should be " project total by business unit divided by the project total"

That sounds like sum(total <[business unit]> amount)/sum(total amount)

If not, please post a sample document with some data.


talk is cheap, supply exceeds demand
shane_spencer
Specialist
Specialist

I think I understand what you're after. Project and  Business Unit needs to be Dimensions and the following your expression.

num(Sum(Amount)/Sum(total <[business unit]> Amount),'#0.0%')

If you share your data / application I can confirm / create an example.

Not applicable
Author

Gysbert, Shane,

Thanks for the quick reply. I had to pare down my file to fit within company e-mail attachment standards. I have it attached. After this question is answered I will be able to post another question concerning this file.

Thanks,

Frank

shane_spencer
Specialist
Specialist

Please take a look at the pivot table and straight table I have created as examples on Sheet1. I think this is what you are after, if not please elaborate.

jcampbell
Partner - Creator
Partner - Creator

Another option using Set Analysis is the following:

Num(Sum({<[Year]={'2015'}>} [Sales]) - Sum({<[Year]={'2014'}>} [Sales])) / Sum({<[Year]={'2014'}>} [Sales])