Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vidyasagar159
Creator II
Creator 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
Creator II
Creator II
Author

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

View solution in original post

16 Replies
vidyasagar159
Creator II
Creator II
Author

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

sunny_talwar

Better yet, use this

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

vidyasagar159
Creator II
Creator II
Author

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

Percentage.PNG

Thanks,

-Vidya

sunny_talwar

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

OmarBenSalem

How come?

Capture.PNG

sunny_talwar

add the calculated dimension and you will see what he means

vidyasagar159
Creator II
Creator II
Author

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

Ah I see !

change ur measure to :

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


result:

Capture.PNG

vidyasagar159
Creator II
Creator II
Author

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