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

    Set modifier for multiple selections in fields

      Hi,

       

      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

          Hi

           

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

          Or

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

           

          Hope that helps

          Jonathan

            • Re: Set modifier for multiple selections in fields

              Hi,

               

              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.