topic Aggr and Total in QlikView

Aggr and Total

Mon, 18 Mar 2013 07:37:03 GMT

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

Re: Aggr and Total

Mon, 18 Mar 2013 07:53:13 GMT

hi,

try this

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

Regards,

Nirmal

Re: Aggr and Total

jagan — Mon, 18 Mar 2013 07:58:35 GMT

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.

Re: Aggr and Total

Mon, 18 Mar 2013 08:01:47 GMT

hi,

PFA hope this is helpful for you

Regards,

Nirmal

Re: Aggr and Total

Mon, 18 Mar 2013 08:52:24 GMT

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 %

Re: Aggr and Total

jagan — Mon, 18 Mar 2013 08:59:21 GMT

Hi,

Try this

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

Regards,

Jagan.