3 Replies Latest reply: Jun 19, 2012 3:55 AM by Jonathan Dienst RSS

    Set modifier for multiple selections in fields



      I am new in Qlikview set analysis. I am trying to sum the [PL Item Value] by having the selections both in [PL Item Name]='Revenue' and [Position Date]=max(Year([Position Date])). However, each of the expressions below does not work.


      This expression has error,

      i).     sum({$<[PL Item Name]={'Revenue'},[Position Date]={=max(year([Position Date]))}>}[PL Item Value])


      While this expression returns 0 value,

      ii).    sum({$<[PL Item Name]={'Revenue'},[Position Date]={$(=max(year([Position Date])))}>}[PL Item Value])



      I am building a bar chart which I would like to express the summation of [PL Item Value] of [PL Item Name]='Revenue', meanwhile it is always returning the [PL Item Value] of [PL Item Name]='Revenue' of the most current Year([Position Date]).

      Attached is the data set.


      Please help. Thanks.

        • Re: Set modifier for multiple selections in fields
          Jonathan Dienst



          Your second expression is syntactically correct, but I am not sure that it makes sense. The expression assumesthat Is the field [Position Date] a year value?


          I suspect that you need something like this:


          sum({$<[PL Item Name]={'Revenue'},[Position Date]={'$(=Date(max([Position Date])))'}>} [PL Item Value])


          sum({$<[PL Item Name]={'Revenue'},[Position Date]={'$(=YearStart(max([Position Date])))'}>} [PL Item Value])


          Hope that helps


            • Re: Set modifier for multiple selections in fields



              Both expressions return 0 value of [PL Item Value], instead of 41858.8, for max([Position Date]).

              It does not matter if [Position Date] is a year value or not. As long as the sum([PL Item Value]) is expressed by both the selections of [PL Item Name]='Revenue' and [Position Date]=max([Position Date]).


              I have even created and used a variable vSelectedPositionDate (=if(GetSelectedCount([Position Date]), max(Year([Position Date])), Year([Position Date]))), into the expression, but it is still not working.


              Am I able to set any expression, if I combine the [PL Item Name] and [Position Date] into a single field [PL Date-Item Name]? The new field will be alphanumeric field type, in theory can not be filtered.


              Thanks for your help, Jonathan.