Set Analysis

[Anonymous]

Dear Experts,

I am writing this set analysis formula. The issue is when I am selecting individual records in field "Desc" the expression works fine that is if in list box of Desc I select A then expression result is perfect and also for other different selections but when I select two values of Desc which are in or that when I select Desc value "A" and "B" the expression is not evaluating as expected.

Is there any basic mistake I am doing here as far as set analysis is concerned ?

Any help in this regard is highly appreciated.

if(Desc='A' or Desc='B',

  if(sum({$<ID = {'Q3'}>}Present) = 0,0,sum({$<ID = {'Q3'},ZeroFlag = {'N'}>} Resp) / sum({$<ID = {'Q3'}>}Present))

,

if(Desc='C' or Desc='D',

  if(sum({$<ID = {'Q6'}>}Present) = 0,0,sum({$<ID = {'Q6'},ZeroFlag = {'N'}>} Resp) / sum({$<ID = {'Q6'}>}Present))

  ,

  if(sum({$<ID = {'Q3'}>}Present) = 0,0,(sum({$<ID = {'Q6'},Desc = {'C' ,'D'},ZeroFlag = {'N'}>} Resp) + sum({$<ID = {'Q3'},Desc = {'A','B'},ZeroFlag = {'N'}>} Resp))

  / (sum({$<ID = {'Q6'},Desc = {'C' ,'D'}>}Present)+ sum({$<ID = {'Q3'},Desc = {'A','B'}>}Present)))

  )

  )

Regards,

RG

[johnw]

I'm guessing this is an expression in a chart? If Desc is not a dimension of that chart, then selecting two values for Desc makes Desc evaluate to null. Null doesn't match any of your conditions, so you might end up in the third "if" when you don't expect to.

Nothing is jumping out at me about the set analysis, but it's a long expression, and may not be where your problem lies.

[Anonymous]

Hi John,

Thanks for your reply.

Yes I am using this expression in a bar chart.

Desc is part of my Drill Down group which is one of the dimensions I am using for this chart.

Regards,

RG

[swuehl]

Could you describe a bit more detailed how the result deviates from your expectation (i.e. what do you expect to see and what do you see) when you perform the different selections?

Would it be feasible that you also upload a small sample QVW?

Or a rough description of your data model? Some screenshots?

[sunny_talwar]

Can't be entirely sure, but may be this:

if(Desc='A' or Desc='B',

  if(sum({$<ID = {'Q3'}>}Present) = 0,0,sum({$<ID = {'Q3'},ZeroFlag = {'N'}>} Resp) / sum({$<ID = {'Q3'}>}Present))

,

if(Desc='C' or Desc='D',

  if(sum({$<ID = {'Q6'}>}Present) = 0,0,sum({$<ID = {'Q6'},ZeroFlag = {'N'}>} Resp) / sum({$<ID = {'Q6'}>}Present))

  ,

  if(sum({$<ID = {'Q3'}>}Present) = 0,0, RangeSum(sum(TOTAL{$<ID = {'Q6'},Desc = {'C' ,'D'},ZeroFlag = {'N'}>} Resp), sum(TOTAL{$<ID = {'Q3'},Desc = {'A','B'},ZeroFlag = {'N'}>} Resp))

  / RangeSum(sum(TOTAL{$<ID = {'Q6'},Desc = {'C' ,'D'}>}Present), sum(TOTAL{$<ID = {'Q3'},Desc = {'A','B'}>}Present)))

  )

  )

[Anonymous]

Hi Sunny,

I tried to use the expression you suggested but the result is same.

I will create a mock up qwv and post here so that you can help me with your expertise on that.

Thanks for your time.

Regards,

RG

[Anonymous]

Hi Swuehl,

I will create a mock up qwv and post here so that you can help me with your expertise on that.

Thanks for your time.

Regards,

RG