topic Re: Set expression to exclude selection of one particular field in QlikView

Set expression to exclude selection of one particular field

Pankaj_Qlik — Sat, 19 Sep 2015 07:23:55 GMT

I need set expression to calculate average of some index from year start to maximum date range. While my requirement is that it should exclude name field selection.

Example:

LOAD * INLINE [

Assistant Name, Source, DateNum, Performance Index

aaa, PerformanceIndex, 1/1/2015, 0.95

bbb, PerformanceIndex, 5/13/2015, 0.62

ccc, PerformanceIndex, 9/18/2015, 0.82

ddd, PerformanceIndex, 9/19/2015, 0.75

];

For above sample data, this is my expression I tried out, but its not working.

=avg({<[Assistant Name]=,Source={'PerformanceIndex'},DateNum={">=$(=Num(YearStart(max(DateNum))))<=$(=max(DateNum))"}>}[Performance Index])

On selecting 'Assistant Name' filter, changes gets reflected for the average, but I want to avoid that.

Any solution for this?

Thanks in advance,

Pankaj

Re: Set expression to exclude selection of one particular field

swuehl — Wed, 21 Oct 2015 17:29:00 GMT

That's because your DateNum set modifier search expression reacts on selections (max(DateNum) )

Try

=avg({<[Assistant Name]=,Source={'PerformanceIndex'},DateNum={">=$(=Num(YearStart(max({1} DateNum))))<=$(=max({1} DateNum))"}>}[Performance Index])

=avg({<[Assistant Name]=,Source={'PerformanceIndex'},DateNum={">=$(=Num(YearStart(max({<<[Assistant Name]=,Source={'PerformanceIndex'}>} DateNum))))<=$(=max({<[Assistant Name]=,Source={'PerformanceIndex'}>} DateNum))"}>}[Performance Index])

Re: Set expression to exclude selection of one particular field

sunny_talwar — Wed, 21 Oct 2015 17:29:16 GMT

Try if this helps:

=avg({<[Assistant Name]=,Source={'PerformanceIndex'},DateNum={">=$(=Num(YearStart(max({<[Assistant Name]=>}DateNum))))<=$(=max({<[Assistant Name]=>}DateNum))"}>}[Performance Index])

Re: Set expression to exclude selection of one particular field

maxgro — Wed, 21 Oct 2015 17:36:42 GMT

=avg({<

[Assistant Name]=,

Source={PerformanceIndex},

DateNum={">=$(=Date(Num(YearStart(max({[Assistant Name]=}DateNum)))))<=$(=Date(max({[Assistant Name]=}DateNum)))"}

[Performance Index])