Based on month concatenate month + string

[nicholas5141]

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]

May be this:

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

[swuehl]

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 · Accepted solution]

may be like this:

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

[mohammadkhatimi]

Try this...

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

Hope this will helps u...!!!

Regards,

Mohammad

[nicholas5141]

Hey, thanks work perfectly.