Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

vidyasagar159
Contributor II

Calculate Percentage of Multidimensional columns

Hello Community,

I want to create a Percentages based on the range of Amount by Year and Company. Please find the details below. I tried a couple of approaches but I am not able to achieve the expected result.

Actual Table:

YearCompanyAmount
2010Apple10
2010Nokia20
2010Samsung30
2010Apple100
2010Nokia200
2010Apple500
2011Nokia100
2011Samsung200
2012Apple500
2012Nokia150
2014Apple600
2014Nokia100
2014Samsung150
2014Apple300
2014Nokia200

Result Table: This is giving me the percentages of all the years.

YearCompanyPlan SizeTotal AmountPercentages
Totals3160100%
2010AppleGreate than 50061019%
Nokia50-7002207%
SamsungLess then 50301%
2011Nokia100-2001003%
Samsung100-2002006%
2012Apple50-70050016%
Nokia100-2001505%
2014AppleGreate than 50090028%
Nokia50-7003009%
Samsung100-2001505%

Dimension Expression:

Plan Size:


if(aggr(sum(Amount),Year,Company)<50,'Less then 50',

if(aggr(sum(Amount),Year,Company)>=100 and aggr(sum(Amount),Year,Company)<=200 ,'100-200',

if(aggr(sum(Amount),Year,Company)>500,'Greate than 500',

if(aggr(sum(Amount),Year,Company)>50 and aggr(sum(Amount),Year,Company)<700 ,'50-700',

))))

Measure Expression:

Total Amount:

(sum(total<Year,Company>Amount))

Percentage Amount:

sum(Amount)/(sum(total{$<Year,Company>}Amount))

But the Expected Result I want is to show the 100% split per each year.

Expected Result:

YearCompanyPlan SizeTotal AmountPercentages
2010AppleGreate than 50061071%
2010Nokia50-70022026%
2010SamsungLess than 50303%
Total 860100%
2011Nokia100-20010033%
2011Samsung100-20020067%
Total 300100%
2012Apple50-70050077%
2012Nokia100-20015023%
Total 650100%
2014AppleGreate than 50090067%
2014Nokia50-70030022%
2014Samsung100-20015011%
Total 1350100%

Also, I will convert this table to 100% Stacked bar color by PLAN SIZE. So that I can see how much percentage of plan size for each year.

Thanks in Advance,

-Vidya

1 Solution

Accepted Solutions
vidyasagar159
Contributor II

Re: Calculate Percentage of Multidimensional columns

So, With all the respect and information provided by the gentlemen in this thread. Both the expressions will give the right output.

Measure Expressions:

Sum(Amount)/Aggr(nodistinct sum(Amount),Year)

OR

sum(Amount) / aggr(sum(total <Year> Amount),Year,Company)

Since my report consuming more than 10 Million records of data. I have to test which is the better one in terms of

performance.

Thanks,

-Vidya

16 Replies
vidyasagar159
Contributor II

Re: Calculate Percentage of Multidimensional columns

Hi All,

I was able to find the solution. All I need to change the percentage expression. Which is.

sum(Amount)/aggr(nodistinct sum(Amount),Year).

However, I have to convert this result set into a 100% stacked bar.

Thanks,

-Vidya

Re: Calculate Percentage of Multidimensional columns

Better yet, use this

Sum(Amount)/Sum(TOTAL <Year>Amount)

vidyasagar159
Contributor II

Re: Calculate Percentage of Multidimensional columns

I tried this expression but I am getting the wrong Percentage split.

Percentage.PNG

Thanks,

-Vidya

Re: Calculate Percentage of Multidimensional columns

Seems like something to do with the calculated dimension.... Not sure why it creates an issue

OmarBenSalem
Esteemed Contributor

Re: Calculate Percentage of Multidimensional columns

How come?

Capture.PNG

Re: Calculate Percentage of Multidimensional columns

add the calculated dimension and you will see what he means

vidyasagar159
Contributor II

Re: Calculate Percentage of Multidimensional columns

Yes. If you add the calculated dimension the results will vary.

if(aggr(sum(Amount),Year,Company)<50,'Less then 50',

if(aggr(sum(Amount),Year,Company)>=100 and aggr(sum(Amount),Year,Company)<=200 ,'100-200',

if(aggr(sum(Amount),Year,Company)>500,'Greate than 500',

if(aggr(sum(Amount),Year,Company)>50 and aggr(sum(Amount),Year,Company)<700 ,'50-700',

))))

Thanks,

-Vidya

OmarBenSalem
Esteemed Contributor

Re: Calculate Percentage of Multidimensional columns

Ah I see !

change ur measure to :

sum(Amount) / aggr(sum(total <Year> Amount),Year,Company)


result:

Capture.PNG

vidyasagar159
Contributor II

Re: Calculate Percentage of Multidimensional columns

Hi,

After converting the Pivot table to a 100% stacked bar chart. I got it what I want. . There might be a better solution than this. But as of now, this solution helped me in fixing my issue.

Percentage Stacked Bar.PNG

Thanks,

-Vidya

Community Browser