pivot : get previous december value in january

[amien]

I have this pivot:

customer, year, period

in my expression i have this:

if(Period=2,(sum([01_JAN_Value])),

if(Period=3,(sum([02_FEB_Value])),

if(Period=4,((sum([03_MAR_Value))),

I know this is not ideal, but just the 'as-is' situation. But the issue is with period 1. It needs to show there 12_DEC_Value.

If(Period=1,SUM(12_DEC_Value) will get me current year. And i need previous year, regarding the selection that the user made.

Thanks in advanced

[Gysbert_Wassenaar]

I think you should create a serial period field in the script. Such a serial field would increase by 1 for every following period crossing year borders. The advantage of such a field is that subtracting 1 from a value always gets you the previous period. You can use something like autonumber(period&year) as PeriodSerial.

If you have a date field in your data you can use the Qlikview Components library to automagically create a master calendar for you from that date field. You'll get the serial month field for free. See this tutorial for information.

[amien]

Gysbert,

Thanks for your reply.

I know autonumber and master calendar. But i don't see how i can use this with dynamic sum of fields base on the current period of the pivot.

[01_JAN_Value] are forecast version. So when the pivot is on the 2013-04 row. i want to see the forecast version of march ([03_MAR_Value]). This is M1 (one month back). But i also need to add a second expression with M3 (3 months back).

how would this expression look like with an autonumber for M1?

[Gysbert_Wassenaar]

Ah, never mind. I didn't read your post correctly. Your data model is more screwed up than I thought it was

Maybe this will work: If(Period=1,above(total SUM(12_DEC_Value)).

If it doesn't can you post a representative sample document?

[amien]

Gysbert,

Yes, the value is correct, but now i have the problem, that when a user selects a year, period 1 is empty, because there is no ABOVE value. 

And i think i can't work with set analysis, because the set analysis is only evaluated ones/not evaluated with the context of the dimensions (and i need a different $(Year)-1 for each period 1). Because 2011-P1 should show 2009-P12 and 2012-P1 should show 2011-P12

[Gysbert_Wassenaar]

Yeah, above() can't reach records that aren't in the chart. Set expressions are possible, but very messy. You'll have to create a set modifier for each period and use the value of the period to pick the right one. That's either a massive nested if expression or a complicated pick-match combination.

The best way out of this is probably modifying your data model. Perhaps it can be as simple as using the previous() function to use the previous value of 12_DEC_Value instead of the current value if the period is 1. But you may have to make larger changes. Maybe an AsOf table approach can help.

[amien]

I ended up with changing the datamodel.

PREVIOUS would work, but then you have the problem with customers,material. You have to add things like IF(PREVIOUS(Customer) = Customer etc.

In the end i added PeriodID key (Year*12+Month) and then do a left join on PeriodID-1, with all the needed dimensions and the expression fields that i needed.