3 Replies Latest reply: Apr 18, 2013 4:36 PM by Jose Tos RSS

    Set analysis - concatenating

    Steve Zagzebski

      I have the following expression that works fine:


      Sum ({<[Incurred Year] = {$(=Max([Incurred Year]))}>}[Car Sales])


      However, I need to make it so that the above changes so that the left side of the equation is <[Incurred Year Month] and now the right side needs to still be the max of the Incurred Year field BUT also concatenated with the field [Renewal Month].


      In non-Qlikview terms:


      [Incurred Year Month] = max([Incurred Year] & [Renwal Month]

        • Re: Set analysis - concatenating
          Jose Tos

          I didn't try but you could use this.Check the MonthDate format is the same that [Incurred Year Month] and the ()...


          Sum ({<[Incurred Year Month] = {$(=MonthName(makedate(Max([Incurred Year]),[Renwal Month],1),'MMM/YYYY'))}>}[Car Sales])


          hope this helps

            • Re: Set analysis - concatenating
              Steve Zagzebski

              Thanks for the response. Unfortunately didn't work.


              To be clear on the data:


              "Incurred Year" is a 4 digit text field (ex 2013)

              "Incurred Year Month" is 6 digit text field (ex 201302)

              "Renwal Month" is 2 digit text field (ex 02)

                • Re: Set analysis - concatenating
                  Jose Tos

                  Have you tried to change the format to YYYYMM.

                  The idea is the next: makedate(2013, 02,01) return a date in your default date format, using MonthName() to that date would return Feb 2013, but you need to get 201302 so we use MonthName( date, 'YYYYMM').

                  Maybe there is a syntax problem but I think is something like this:


                  Sum ({<[Incurred Year Month] = {$(=MonthName(makedate(Max([Incurred Year]),[Renwal Month],01),'YYYYMM'))}>}[Car Sales])