topic Re: Calculate % of Total Expression in QlikView

Calculate % of Total Expression

Wed, 18 Sep 2013 07:52:18 GMT

I have a straight table, with Customer dimension and Sum(Sales) as expression.

I want another Expression that should show me that Customer sales as a % of Total Sales. I am unable to figure out how to do that. Any help is appreciated.

Customer	Sales	% Sales
A	10	10%
B	20	20%
C	30	30%
D	40	40%
Total	100

Thanks,
Sagar

Re: Calculate % of Total Expression

abeyphilip — Wed, 18 Sep 2013 07:56:37 GMT

Hi Sagar,

For '%' expression:

sum (sales) / sum (total sales)

Then in number tab, show as %.

Regards,

Abey

Re: Calculate % of Total Expression

Wed, 18 Sep 2013 08:07:18 GMT

Abey,

I tried Sum( total Sales) but that doesnt give right answer as the total takes all the values in the database and not only the ones I have selected in my table. For example, my database looks like this:

Customer	Sales
A	-10
B	-20
C	-30
D	-40
E	10
F	20
Total Sales	-70

In my dimension, I want to show only the negative sales.

Customer	Sales
A	-10
B	-20
C	-30
D	-40
Total -ve Sales	-100

Then I want to show % for each of the rows against -ve sales.

Sum(Total Sales) is giving me -70, while I want the total for only the rows selected in my dimension. I hope I am able to ask the question correctly.

Re: Calculate % of Total Expression

kangaroomac — Wed, 18 Sep 2013 08:09:25 GMT

Hi Sagar,

To create the % column, do as Abey said above. If you want to see the relative % (i.e. of Total Sales, Customer A Contributes x %, etc.), select the "Relative" checkbox in your expressions tab of your straight table properties.

Hope this helps.

Re: Calculate % of Total Expression

Wed, 18 Sep 2013 08:20:42 GMT

Thanks Abey, Ian. Selecting the 'Relative' checkbox solved my problem.

My next task if to have a slider with these relative % values. Is that even possible?

Re: Calculate % of Total Expression

kangaroomac — Wed, 18 Sep 2013 08:40:59 GMT

Hi Sagar,

I'm not too sure, I've never attempted something similar. I would suspect it might be a bit easier if you created the field in the load script, and then used that field in your slider.

Hope this steers you in the right direction.

Re: Calculate % of Total Expression

Wed, 18 Sep 2013 08:48:10 GMT

Ian,

I attempted something on these line. I created a variable and then created a slider that changes this variable. The range of the variable is 0% to 100%.

Now, I want to show the above mentioned table but only those dimensions that are selected in the slider:

Customer	Sales	% Sales
A	-10	10%
B	-20	20%
C	-30	30%
D	-40	40%

So, if the slider is selected at 40%, I want to filter the dimension to show only Customer D.

Re: Calculate % of Total Expression

kangaroomac — Wed, 18 Sep 2013 09:02:17 GMT

Hi Sagar,

One way of doing it, and I'm not sure if this is what you're after, is having an IF statement in the Expression of the Sales Column, i.e.:

Sum(IF((Sum(Sales) / Sum(TOTAL Sales)) >= $(yourVariable), Sales)

This would give you the following scenario: If a user selects 40% for the slider, D will show as requested. If a user selects 20%, B, C and D should be shown.

Re: Calculate % of Total Expression

Wed, 18 Sep 2013 11:10:15 GMT

Hi Ian,

The problem with Sum(Total Sales) is that it does a total on all the rows present in the database, and not only the ones that I have selected in the dimension. So, for example, if the Customers shown above are only from 1 region (i have specified an IF clause in dimension), the Sum(Total Sales) will show all sales from all regions. Is there a way to get Total Sales only for the Customers present in the dimension? If yes, then I can implement your above recommendation.

Re: Calculate % of Total Expression

Wed, 18 Sep 2013 12:20:57 GMT

Hi all,

I am attaching a test file, where I have tried to implement Sum(Total Sales). As you can see, it is giving incorrect value. Please suggest a way so that I can use my slider to show only the relevant values in my straight table.

Re: Re: Calculate % of Total Expression

kangaroomac — Thu, 19 Sep 2013 07:04:10 GMT

Hi Sagar,

I'm not quite sure I'm following what you would like to do, but I have made some assumptions and a possible solution for you could be to :

Create a field for your sales in the load script (i.e. Region based or whatever your source looks like)
- In the attached example I called it Negative_Sales
- When you use the Total keyword now, it is only the total of the total you are after
Then on the front end I changed the expression to be the one created in the load script
I also put in a logic field, that tests whether it is larger than or equal to the variable connected to the slider
- In this example just toggles between Yes and No, depending on the slider, but in real world, an option could be to show a dimension based on the selection and then choosing suppress when nulll

Hope this helps.