Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
may be like this:
=If(Month(Date) > 6, 'Year End ','Mid Year ') & Year(Date)
May be this:
=If(Ceil(Month(Date)/6) = 1, 'Mid Year ', 'Year End ') & Year(Date)
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 ...;
may be like this:
=If(Month(Date) > 6, 'Year End ','Mid Year ') & Year(Date)
Try this...
=If(Month(Date) > 6, 'Year End ','Mid Year ') & Year(Date) as NewDate
Hope this will helps u...!!!
Regards,
Mohammad
Hey, thanks work perfectly.