topic Re: Calculate percent by dimension in QlikView

Calculate percent by dimension

simonagheo — Sat, 16 Nov 2024 00:23:54 GMT

Hello,

I have a table that stores some dimension codes and their values:

Dimension	Value
D1	10
D1	90
D2	20
D4	35
D4	5
D6	20

and another table that stores their names.

I need a table with their sum but I also have to insert other dimensions that represent some percents based on the existing ones, like D3, D5, D7.

Dimension	Name	Value
D1	Total	100
D2	Dim 2	20
D3	Dim 2 %	20%
D4	Dim 4	40
D5	Dim 4 %	40%
D6	Dim 6	20
D7	Dim 6 %	20%

I tried to make a pivot table with the following expression:

=if(Match(Dimension,'D3'),num(sum({<Dimension={'D2'}>}Value)/sum({<Dimension={'D1'}>}Value),'#.##0%',',','.'),

if(Match(Dimension,'D5'),num(sum({<Dimension={'D4'}>}Value)/sum({<Dimension={'D1'}>}Value),'#.##0%',',','.'),

f(Match(Dimension,'D7'),num(sum({<Dimension={'D6'}>}Value)/sum({<Dimension={'D1'}>}Value),'#.##0%',',','.'),

,num(sum(Value),'#.##0',',','.'))))

but this isn't working.

Can you help me find a better solution for my problem?

Thank you!

Re: Calculate percent by dimension

mdmukramali — Tue, 30 Jun 2020 21:55:45 GMT

Hi,

can you try something like below:

Script:

LOAD Dimension,
Value
FROM
[https://community.qlik.com/t5/QlikView-App-Development/Calculate-percent-by-dimension/td-p/1723712]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

LOAD * Inline
[
Dimension
D3
D5
D7
];

Expression:

=IF(Dimension='D3',((Sum({<Dimension = {'D2'}>}TOTAL Value))*20/100),
IF(Dimension='D5',((Sum({<Dimension = {'D4'}>}TOTAL Value))*40/100),
IF(Dimension='D7',((Sum({<Dimension = {'D6'}>}TOTAL Value))*20/100),
sum(Value))))

Thanks,

Mohammed Mukram

Re: Calculate percent by dimension

Saravanan_Desingh — Wed, 01 Jul 2020 01:45:20 GMT

Why there is no % Row for D1? Why are you skipping D1?

Re: Calculate percent by dimension

simonagheo — Wed, 01 Jul 2020 10:38:11 GMT

@Saravanan_Desingh Because for some dimensions that percent doesn't make any sense and for others the formula is changhing. I also have D10 and D11 with data, and D12 should be calculated as D11/D10, D14=D13/D10 and so on..

Re: Calculate percent by dimension

simonagheo — Wed, 01 Jul 2020 10:36:56 GMT

Thank you, @mdmukramali ! It works when nothing is selected, but when I select an year or a month, the percents disappear. I tried to use aggr function to fix it, but I can't figure out how to use it.

Re: Calculate percent by dimension

mdmukramali — Wed, 01 Jul 2020 10:41:33 GMT

Hi @simonagheo ,

can you upload sample data file.

Re: Calculate percent by dimension

simonagheo — Thu, 02 Jul 2020 01:07:23 GMT

Hi, @mdmukramali !

I attached the sample.

Re: Calculate percent by dimension

Saravanan_Desingh — Thu, 02 Jul 2020 03:08:33 GMT

One solution in the Script.

tab1: LOAD * INLINE [ Dimension, Value D1, 10 D1, 90 D2, 20 D4, 35 D4, 5 D6, 20 ]; Left Join(tab1) LOAD Dimension, Sum(Value) As SumValue Resident tab1 Group By Dimension; Outer Join(tab1) tab2: LOAD * INLINE [ Dimension, Name D1, Total D2, Dim 2 D3, Dim 2 % D4, Dim 4 D5, Dim 4 % D6, Dim 6 D7, Dim 6 % ]; Mapper: Mapping LOAD Distinct Name, SumValue Resident tab1 Where Not IsNull(SumValue) ; Result: LOAD DISTINCT Dimension, Name, MapSubString('Mapper',Name) As Result Resident tab1; Drop Field Value; Drop Table tab1;

Re: Calculate percent by dimension

Saravanan_Desingh — Thu, 02 Jul 2020 03:09:28 GMT

Output.

Re: Calculate percent by dimension

Brett_Bleess — Wed, 15 Jul 2020 14:19:18 GMT

Did Saran's last post get you a working solution, if so, we would appreciate it if you would close out the thread by using the Accept as Solution button on his post, which will give him credit for the help and let other Community Members know that worked. If you did something else, you can post what you did and use the button to mark that post, and if you are still trying to figure things out, leave an update with what you still need.

You can check out the following area of Community too, lots of how-to related posts there with examples and stuff:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett

Re: Calculate percent by dimension

simonagheo — Tue, 12 Jan 2021 11:05:17 GMT

No, it is not the solution I needed. I didn't solve the problem.

Thank you!

Re: Calculate percent by dimension

simonagheo — Tue, 12 Jan 2021 11:09:37 GMT

Thank you, but it is not the solution I needed, because D3 should be D2/D1, so, when

D1=80,

D2=20,

D3 should be D2/D1=20/80=25%, and your solution calculates D3 as 20%.