topic Re: Set Analysis in QlikView

Set Analysis

Anonymous — Tue, 19 Apr 2016 20:29:12 GMT

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,

Re: Set Analysis

johnw — Tue, 19 Apr 2016 21:05:25 GMT

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.

Re: Set Analysis

Anonymous — Tue, 19 Apr 2016 21:13:26 GMT

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,

Re: Set Analysis

swuehl — Tue, 19 Apr 2016 22:48:56 GMT

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?

Re: Set Analysis

sunny_talwar — Tue, 19 Apr 2016 23:06:15 GMT

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)))

)

Re: Set Analysis

Anonymous — Thu, 21 Apr 2016 20:12:12 GMT

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,

Re: Set Analysis

Anonymous — Thu, 21 Apr 2016 20:12:46 GMT

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,