Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Valued Contributor

Re: Re: Using set analysis to calculate percentage

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.

5 Replies

Re: Using set analysis to calculate percentage

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
Valued Contributor

Re: Using set analysis to calculate percentage

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

Re: Using set analysis to calculate percentage

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
Valued Contributor

Re: Re: Using set analysis to calculate percentage

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
Contributor

Re: Re: Using set analysis to calculate percentage

Another option using Set Analysis is the following:

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

Community Browser