Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
scotly-victor
Contributor II

Set Analysis Error

Hi every one,

I have a table fields Month Year and Amount.

  

Month YrAmount
Aug-15250
Sep-15225
1-Oct450
1-Nov375
Dec-15275
1-Jan400
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 ?

Tags (1)
6 Replies

Re: Set Analysis Error

Not sure, but may be like this:

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


Capture.PNG

MVP
MVP

Re: Set Analysis Error

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

MVP
MVP

Re: Set Analysis Error

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

scotly-victor
Contributor II

Re: Set Analysis Error

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

scotly-victor
Contributor II

Re: Set Analysis Error

Thank you for you Instant reply

There is no error in Month Yr field

Re: Set Analysis Error

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