topic Set Analysis Roll Up with Rolling 13 Calculated Dimension? in QlikView

Set Analysis Roll Up with Rolling 13 Calculated Dimension?

Anonymous — Wed, 12 May 2010 18:34:21 GMT

Data: Master Table - Includes Calendar. I've outer joined our Stores table to our Calendar table, thus creating Master using Store# and Dates. Store# is the base level, then Area, then Division, then Company.

Wage Rate Table - Links to the Master table, on Store number, Year, Period. Includes the # of hires, the sum of their starting rates, as well as the average of all employes wages, broken down by department (department = driver, server, production, management, etc..)

Im using a Rolling 13 Calculated Dimension:

=Num(IF($(vCP)=NUM(Right(FiscalPeriod,2)),13,If($(vCP)<NUM(Right(FiscalPeriod,2)),NUM(Right(FiscalPeriod,2))-$(vCP),13-($(vCP)-NUM(Right(FiscalPeriod,2)))))&'.'&Num(Right(FiscalPeriod,2)))

vCP = Current Period

FiscalPeriod = YearPeriod as in 201005.

The Calculation creates a rolling 13 based on the selected Period. Meaning if I select Year = 2009 and Period = 4, the dimensions are 1.5, 2.6, 3.7, 4.8, 5.9, 6.10, 7.11, 8.12, 9.13, 10.1, 11.2, 12.3, 13.4. Simply put into words, # In Sequence.Period.

My expression includes set modifiers to filter by selected stores, as well as selected year period.

=(Sum ( {1< FiscalPeriod ={">=$(=Max(FiscalPeriod)-99) <=$(=Max(FiscalPeriod))"}, Department= {'57'}, StoreNo = {"$(=GetFieldSelections(StoreNo))"} >} StartPayRateSum)) / (Sum ( {1< FiscalPeriod ={">=$(=Max(FiscalPeriod)-99) <=$(=Max(FiscalPeriod))"}, Department= {'57'}, StoreNo = {"$(=GetFieldSelections(StoreNo))"} >} StartPayRateNumEmp))

How can I get this to roll up in the Hierarchy described here: Store# is the base level, then Area, then Division, then Company.

Also how do can make it to where I can select multiple stores, and have the calculation still work

This is being displayed in a line graph. The visual representation show over 13 periods the fluxuation of Pay rates.

Set Analysis Roll Up with Rolling 13 Calculated Dimension?

Anonymous — Wed, 12 May 2010 21:23:59 GMT

NOTE: Store# 12243, and 40000 are not legit store#s.

I uploaded what Im working with. How can I get it to roll up?

Bueller?

Set Analysis Roll Up with Rolling 13 Calculated Dimension?

Anonymous — Thu, 13 May 2010 16:30:22 GMT

Can someone at least deny the possibility please?

Set Analysis Roll Up with Rolling 13 Calculated Dimension?

johnw — Thu, 13 May 2010 20:19:29 GMT

OK, trying to make sense of what you're doing.

So FiscalPeriod is YYYYMM, except that you didn't make it a date, just a number. I'd have done this:

date(date#(FiscalPeriod,'YYYYMM'),'YYYYMM') as FiscalPeriod

Then you have num(right(FiscalPeriod,2)) all over the place. I'd have done something like this during the load:

month(date#(FiscalPeriod,'YYYYMM')) as FiscalMonth

Now let's see if I can make more sense of your calculated dimension:

Num(IF($(vCP)=NUM(Right(FiscalPeriod,2)),13
,IF($(vCP)<NUM(Right(FiscalPeriod,2)),NUM(Right(FiscalPeriod,2))-$(vCP)
,13-($(vCP)-NUM(Right(FiscalPeriod,2)))))&'.'&Num(Right(FiscalPeriod,2)))

Would become this:

num(if($(vCP)=FiscalMonth,13
,if($(vCP)<FiscalMonth,FiscalMonth-$(vCP)
,13-($(vCP)-FiscalMonth)))&'.'&FiscalMonth)

Which simplifies to this:

num(mod(FiscalMonth-$(vCP)-1,13)+1&'.'&FiscalMonth)

Which you could make into a table in the script by converting vCP into a field instead of a variable:

vCP,FiscalMonth,SomePeriodThing
1,1,13.1
1,2,1.2
1,3,2.3
1,4,3.4
...
4,1,10.1
4,2,11.2
4,3,12.3
...

And then your dimension is just this:

SomePeriodThing

So far so good I hope. Now what was your question? Something about this expression?

(Sum ( {1< FiscalPeriod ={">=$(=Max(FiscalPeriod)-99) <=$(=Max(FiscalPeriod))"}, Department= {'57'}, StoreNo = {"$(=GetFieldSelections(StoreNo))"} >} StartPayRateSum)) / (Sum ( {1< FiscalPeriod ={">=$(=Max(FiscalPeriod)-99) <=$(=Max(FiscalPeriod))"}, Department= {'57'}, StoreNo = {"$(=GetFieldSelections(StoreNo))"} >} StartPayRateNumEmp))

OK, max(Fiscalperiod) is going to be some number like 201004. Subtract 99 is the same as subtract 100 add 1, so you're trying to subtract a year and add a month. Except that it doesn't work, because 201012 - 99 = 200913. There's no 13th month. Unless somehow that's part of your rolling 13? Gah, yes, you do indeed have a 200913 FiscalPeriod, whatever that means. In any case, it means all the work I've done so far is wrong.

So I give up. I can't decipher what you're currently doing, so I can't even get to the point of figuring out what you're TRYING to do, and then trying to solve the problem, whatever the problem is. I bet it isn't anything difficult, but that's easy to say when I don't even understand what you want.

Set Analysis Roll Up with Rolling 13 Calculated Dimension?

Anonymous — Thu, 13 May 2010 20:45:31 GMT

We have 28 days per period, and 13 periods per year. That is why we have the convoluted structure to our Fiscal Year over 13 periods Calculated dimension. The 1.13, 2.1, 3.2, etc should end with 13.12 if period 12 is selected. Say year 2009 is selected, then 1.13 would = period 13 of 2008 and 13.12 would be period 12 of 2009, which should be the last Dimension on the Line Graph.

I have it working based on Selecting Year and Period and StoreNo.

Say we have 7 stores in an Area, 5 areas per division, and 3 divisions within the company. All I want to do is get the Set Analysis to roll up within the Hierarcy of our Company Structure.

I assume it isn't possible unless I include Area, Division, and Company into the Set Modifiers.