QlikView set Analysis for Data Range

[gouthamkk]

HI,

I wrote a set analysis expression as below

=Avg({<Date= {'>=$(=AddMonths(Max(MonthName),-24)) <=$(=MonthEnd(Max(MonthName),-1))'}>} val)

when no selections made i am getting correct result. but if i select a MonthName(eg: Jun 2016-- ListBox), the expression is not displaying any result.

If i select any month name, the expression has to to average of all past 24 months.

Can some one please Advise.

[swuehl]

Try

=Avg({<Date= {'>=$(=AddMonths(Max(MonthName),-24)) <=$(=MonthEnd(Max(MonthName),-1))'}, MonthName=>} val)

i.e. clear selection in MonthName in your set expression to avoid an incompatible set (note that your Date field date range does not cover your active selection).

[gouthamkk]

Yes, i tried this but i also want a flexibility to select date field, that's why in the second part of the expression i am using MonthEnd() function(Bold letters below)

Avg({<Date= {'>=$(=AddMonths(Max(MonthName),-24)) <=$(=MonthEnd(Max(MonthName),-1))'}, MonthName=>} val)

[swuehl]

Not sure if I understand your issue. The expression should still be sensitive to selection in MonthName field (the max() function in the dollar sign expansions should return your max selected value).

You still get no result when you are selecting a value? Could you upload a small sample QVW?

[gouthamkk]

when i select a value from Year list box(eg: 2016) the values is changing and i am getting the average for only 2016 data set.

i still want my expression to calculate for past 24 months starting from max of the month.

i really appreciate all you help

[swuehl · Accepted solution]

Just add all calendar fields the use may select in to your set analysis:

Avg({<Date= {'>=$(=AddMonths(Max(MonthName),-24)) <=$(=MonthEnd(Max(MonthName),-1))'},MonthName=, Year= >} val)