Set Analysis Question

[#1]

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

[PrashantSangle]

Hi,

What is your current expression for calculating total???

Regards

[#3]

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

[#4]

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

Thanks

[maxgro]

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)

  )

[qlikviewwizard · Accepted solution]

[#7]

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

[qlikviewwizard]

Hi Naresh,

Please close the discussion by selecting Correct answer and Helpful.