topic Re: Unusual Calendar Result in QlikView

Unusual Calendar Result

Karl_Hart — Tue, 15 Dec 2015 15:20:58 GMT

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

Re: Unusual Calendar Result

Peter_Cammaert — Tue, 15 Dec 2015 15:46:57 GMT

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

What should be displayed?

Peter

Re: Unusual Calendar Result

rubenmarin — Tue, 15 Dec 2015 16:04:08 GMT

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

Re: Unusual Calendar Result

Karl_Hart — Wed, 16 Dec 2015 09:17:49 GMT

Perfect, many thanks