topic Re: Set Analysis Question in QlikView

Set Analysis Question

Thu, 24 Sep 2015 10:47:06 GMT

I have the following data as shown in the table below.

CNS=ANS - WANS

CR= AR-WAB

While calculating the total, I need only those months selected where ANS <>0 and WANS <>0. I am trying to setup the set analysis for this a need some help. If anyone can suggest the appropriate set analysis statement, I would appreciate it.

I am getting the value of 6.22 while I should get 4.98 when calculating the Totals


Month	ANS	AR	WANS	WAB	CNS	CR	CNS/CR
Jan-15	25	10	0	0	25	10	2.50
Feb-15	100	23	11	3	89	20	4.45
Mar-15	209	43	29	9	180	34	5.29
Apr-15	0	0	50	13	-50	-13	3.85
May-15	0	0	76	24	-76	-24	3.17
Total	334	76	166	49	168	27	6.22
Correct Total	309	66	40	12	269	54	4.98

Re: Set Analysis Question

PrashantSangle — Thu, 24 Sep 2015 10:55:47 GMT

Hi,

What is your current expression for calculating total???

Regards

Re: Set Analysis Question

Thu, 24 Sep 2015 10:58:41 GMT

The corrected total should select those months where ANS <>0 and WANS <>0

e.g, 309= 100+209 for months Feb and Mar ignoring the other months.

Thanks

Re: Set Analysis Question

Thu, 24 Sep 2015 11:00:51 GMT

The correct expression for calculating the total of 4.98 is 269/54 i.e CNS/CR

Thanks

Re: Set Analysis Question

maxgro — Thu, 24 Sep 2015 11:22:32 GMT

ANS 309

if(Dimensionality()=0,

sum(aggr(if(sum(ANS)>0 and sum(WANS)>0, sum(ANS)),Month)),

sum(ANS)

)

WANS 40

if(Dimensionality()=0,

sum(aggr(if(sum(ANS)>0 and sum(WANS)>0, sum(WANS)),Month)),

sum(WANS)

)

Re: Set Analysis Question

qlikviewwizard — Thu, 24 Sep 2015 11:42:03 GMT

Re: Set Analysis Question

Thu, 24 Sep 2015 18:24:41 GMT

Thanks for the replies. I was able to get to what I needed using a combination of both your ideas. Thanks again.

Re: Set Analysis Question

qlikviewwizard — Fri, 25 Sep 2015 01:11:30 GMT

Hi Naresh,

Please close the discussion by selecting Correct answer and Helpful.