Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
Year | Company | Amount |
2010 | Apple | 10 |
2010 | Nokia | 20 |
2010 | Samsung | 30 |
2010 | Apple | 100 |
2010 | Nokia | 200 |
2010 | Apple | 500 |
2011 | Nokia | 100 |
2011 | Samsung | 200 |
2012 | Apple | 500 |
2012 | Nokia | 150 |
2014 | Apple | 600 |
2014 | Nokia | 100 |
2014 | Samsung | 150 |
2014 | Apple | 300 |
2014 | Nokia | 200 |
Result Table: This is giving me the percentages of all the years.
Year | Company | Plan Size | Total Amount | Percentages |
Totals | 3160 | 100% | ||
2010 | Apple | Greate than 500 | 610 | 19% |
Nokia | 50-700 | 220 | 7% | |
Samsung | Less then 50 | 30 | 1% | |
2011 | Nokia | 100-200 | 100 | 3% |
Samsung | 100-200 | 200 | 6% | |
2012 | Apple | 50-700 | 500 | 16% |
Nokia | 100-200 | 150 | 5% | |
2014 | Apple | Greate than 500 | 900 | 28% |
Nokia | 50-700 | 300 | 9% | |
Samsung | 100-200 | 150 | 5% |
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:
Year | Company | Plan Size | Total Amount | Percentages |
2010 | Apple | Greate than 500 | 610 | 71% |
2010 | Nokia | 50-700 | 220 | 26% |
2010 | Samsung | Less than 50 | 30 | 3% |
Total | 860 | 100% | ||
2011 | Nokia | 100-200 | 100 | 33% |
2011 | Samsung | 100-200 | 200 | 67% |
Total | 300 | 100% | ||
2012 | Apple | 50-700 | 500 | 77% |
2012 | Nokia | 100-200 | 150 | 23% |
Total | 650 | 100% | ||
2014 | Apple | Greate than 500 | 900 | 67% |
2014 | Nokia | 50-700 | 300 | 22% |
2014 | Samsung | 100-200 | 150 | 11% |
Total | 1350 | 100% |
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
But then why not just do like how he is doing if we have to use Aggr()
Meaning this
Sum(Amount)/Aggr(nodistinct sum(Amount),Year).
I havnt seen the full discussion.. my bad !
Well, It's 10 pm in France.. so, I'm doing this out of passion; can't blame me if I don't read it all
So, is there any difference between the 2 Expressions? Like performance optimization?
Thanks,
-Vidya
Guys. In the first place, i am really thankful to all of you for looking into this.
From what I see your expression should be slightly better performing because you are aggregating over a single dimension. But I havn't done testing to prove it. May be you can test to see which one works better
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