Unusual Calendar Result

[Karl_Hart]

Hi,

In a previous thread, I asked how to automatically collapse Months into Quarters in the dimension, the answer was given as

If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, Month)

I've tried to implement this fix but I'm coming across an usual issue. I think it may be related to the fact that my fiscal year starts in Apr?!

The solution above works perfectly when using the FiscalMonthNumeric (See the top two charts in the attached) whereas if I use the Month field, it works until I get to January, it then puts January's data in Q1, Feb in Q2 and March in Q3. (The bottom 2 graphs in the attached)

The only difference in the two is one dimension says

If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, Month)

and the other is

If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, FiscalMonthNumeric)

Does anyone have an idea how to fix this?

Thanks

[Peter_Cammaert]

For some reason I'm getting 'Allocated Memory Exceeded' in all four charts if I open your document.

What should be displayed?

Peter

[rubenmarin · Accepted solution]

Hi Kari, I can't explain why but this calculated dimension works in the bottom right chart:

=If(FiscalMonthNumeric<Ceil(Max(TOTAL FiscalMonthNumeric),3)-2,FiscalQuarter, Dual(Month, FiscalMonthNumeric))

[Karl_Hart]

Perfect, many thanks