Set expression to exclude selection of one particular field

[Pankaj_Qlik]

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

[swuehl]

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

or

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

[sunny_talwar]

Try if this helps:

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

[maxgro]

=avg({<

  [Assistant Name]=,

  Source={PerformanceIndex},

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

>}

[Performance Index])