Calculate percent by dimension

[simonagheo]

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!

[mdmukramali]

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

[Saravanan_Desingh]

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

[simonagheo]

@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.. 

[simonagheo]

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.

[mdmukramali]

Hi @simonagheo ,

can you upload sample data file. 

[simonagheo]

Hi, @mdmukramali !

I attached the sample.

[Saravanan_Desingh]

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;

[Saravanan_Desingh]

Output.

[Brett_Bleess]

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