topic Re: Based on month concatenate month + string in QlikView

Based on month concatenate month + string

nicholas5141 — Tue, 24 Nov 2015 10:25:50 GMT

Hi All,

Currently I need help on expression on pivot table to based on month to determine it's year end / mid of year and concatenate with year.

Example: 5/1/13 show in Mid Year 2013

11/1/13 show in Year End 2014

Your kindly assists are appreciated thanks.

sunny_talwar — Tue, 24 Nov 2015 11:19:27 GMT

May be this:

=If(Ceil(Month(Date)/6) = 1, 'Mid Year ', 'Year End ') & Year(Date)

swuehl — Tue, 24 Nov 2015 11:20:57 GMT

Do you want to create an expression or a calculated dimension?

I assume latter and I think it would be best to create an additional field in the data model, something like (assuming september being the threshold

LOAD

Date,

If( Month(Date) < 9,

Dual( 'Mid Year ' & Year(Date), Year(Date)*10 +1),

Dual( 'End Year ' & Year(Date), Year(Date)*10 +2)

) AS Season,

...

FROM ...;

Anonymous — Tue, 24 Nov 2015 11:23:13 GMT

may be like this:

=If(Month(Date) > 6, 'Year End ','Mid Year ') & Year(Date)

mohammadkhatimi — Tue, 24 Nov 2015 11:26:27 GMT

Try this...

=If(Month(Date) > 6, 'Year End ','Mid Year ') & Year(Date) as NewDate

Hope this will helps u...!!!

Regards,

Mohammad

nicholas5141 — Wed, 25 Nov 2015 01:04:40 GMT

Hey, thanks work perfectly.