topic Re: Set Analysis Error in QlikView

Set Analysis Error

scotly-victor — Wed, 25 Nov 2020 16:16:04 GMT

Hi every one,

I have a table fields Month Year and Amount.

Month Yr	Amount
Aug-15	250
Sep-15	225
1-Oct	450
1-Nov	375
Dec-15	275
1-Jan	400
Feb-16
Mar-16
Apr-16

My requirements are :

1.Create a bar chart Month as Dimension and Sum(sales ) as Measures

2.So if there is no sales value then last 3 months average should come for upcoming months with respectively.

My Set Analysis Expression is =if(IsNull(month([Month Yr])),avg({<=(month([Month Yr]))={'Nov','Dec','Jan'}>}Amount),sum(Amount))

I have a Issue in this,Please any one help ?

Re: Set Analysis Error

sunny_talwar — Mon, 04 Apr 2016 12:54:52 GMT

Not sure, but may be like this:

=If(Sum(Amount) = 0, RangeAvg(Above(Sum(Amount), 1, 3)), Sum(Amount))

Re: Set Analysis Error

swuehl — Mon, 04 Apr 2016 13:04:33 GMT

Well, I think a first step in both approaches would be to create a proper Month Yr field with a numeric representation, that you can use as dimension with correct order or in set analysis field modifier

Get the Dates Right

Re: Set Analysis Error

swuehl — Mon, 04 Apr 2016 13:07:35 GMT

Then, with your first approach, you could try something like

=if(Sum(Amount)=0 , avg({< YearMonth = {'Nov 15','Dec 15','Jan 16'}>} TOTAL Amount), sum(Amount))

Re: Set Analysis Error

scotly-victor — Tue, 05 Apr 2016 04:39:17 GMT

Thank you for your Reply,

It was wrong when I gave your expression on Chart, In my Amount field last three values are null, What I want is average of previous three value should be on first null field of chart or table Likewise rest of two fields should be filled .

It is a sort of rolling Average

Re: Set Analysis Error

scotly-victor — Tue, 05 Apr 2016 04:41:24 GMT

Thank you for you Instant reply

There is no error in Month Yr field

Re: Set Analysis Error

sunny_talwar — Tue, 05 Apr 2016 16:21:23 GMT

Not sure what you mean, can you share the expected output from the sample that you have shared with us?