topic Re: Re: Using set analysis to calculate percentage in QlikView

Using set analysis to calculate percentage

Thu, 03 Jul 2014 13:28:47 GMT

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

Re: Using set analysis to calculate percentage

Gysbert_Wassenaar — Thu, 03 Jul 2014 13:33:51 GMT

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.

Re: Using set analysis to calculate percentage

shane_spencer — Thu, 03 Jul 2014 13:41:40 GMT

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.

Re: Using set analysis to calculate percentage

Thu, 03 Jul 2014 15:27:11 GMT

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

Re: Re: Using set analysis to calculate percentage

shane_spencer — Fri, 04 Jul 2014 07:39:34 GMT

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.

Re: Re: Using set analysis to calculate percentage

jcampbell — Tue, 20 Oct 2015 16:05:03 GMT

Another option using Set Analysis is the following:

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