topic Re: QlikView set Analysis for Data Range in QlikView

QlikView set Analysis for Data Range

gouthamkk — Tue, 12 Jul 2016 19:15:39 GMT

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.

Re: QlikView set Analysis for Data Range

swuehl — Tue, 12 Jul 2016 19:21:44 GMT

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

Re: QlikView set Analysis for Data Range

gouthamkk — Tue, 12 Jul 2016 19:29:08 GMT

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)

Re: QlikView set Analysis for Data Range

swuehl — Tue, 12 Jul 2016 19:35:06 GMT

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?

Re: QlikView set Analysis for Data Range

gouthamkk — Tue, 12 Jul 2016 19:43:49 GMT

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

Re: QlikView set Analysis for Data Range

swuehl — Tue, 12 Jul 2016 19:47:35 GMT

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)