Aggr and Total

[#1]

Hi, I have data set like the following , and I am plotting a line chart for comparing A & B %share year by year.

Company  Year        B_num

A               2010       100

B               2010       200

C               2010       500

A               2011       150

B               2011       300

C               2011       200

D               2011       100

A                2012       300

B                2012       350

C                2012       250

D                2012       300

My dimensions are Year and company ( using if condition to get A & B only) . My exp. is sum(B_num)/ sum total<Year>(B_num). I get incorrect % share as the total is considered only from company A and B and not from A,B,C and D. If I include all companies in the chart then it shows correct % share.

How do I handle this as there is another question I will post which related to same situation.

thanks

Lax

[#2]

hi,

try this

num(count({<Company={'A','B'}>}B_num)/sum({<Company={'A','B'}>}B_num),'#0%')

Regards,

Nirmal

[jagan]

Hi,

If you want to consider only

=sum({<Company={'A', 'B'}>}B_num)/ sum({<Company={'A', 'B'}>} total<Year> B_num)

Expression considers company A and B only {<Company={'A', 'B'}>}

Hope this helps you.

Regards,

Jagan.

[#4]

hi,

PFA hope this is helpful for you

Regards,

Nirmal

[#5]

Hi,

     Both of the solution does not give the correct output. I want to show only 2 companies in my graph say A & B, but the total should be considered of all the private companies.

% share per Year for company A and Year 2010,2011 and 2012 should be  12.5%,20% and 25%. Calculation is like this

For Year 2012 : A = 300 and Total is 1200 ( i.e B_num of A+B+C+D=300+350+250+300=1200)

Therefore the % share of A comes to 25 %

[jagan]

Hi,

Try this

=(sum({<Company={A,B}>}B_num)/sum(TOTAL <Year> B_num))

Regards,

Jagan.