topic Re: Set analysis - concatenating in QlikView

Set analysis - concatenating

zagzebski — Thu, 18 Apr 2013 20:13:02 GMT

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

chematos — Thu, 18 Apr 2013 20:24:09 GMT

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

zagzebski — Thu, 18 Apr 2013 20:31:03 GMT

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

chematos — Thu, 18 Apr 2013 20:36:58 GMT

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