Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
scotly-victor
Creator II
Creator 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 ?

6 Replies
sunny_talwar

Not sure, but may be like this:

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


Capture.PNG

swuehl
MVP
MVP

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

swuehl
MVP
MVP

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
Creator II
Creator II
Author

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
Creator II
Creator II
Author

Thank you for you Instant reply

There is no error in Month Yr field

sunny_talwar

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