Set Analysis by dimension in a pivot table?

Thu, 10 Mar 2011 23:53:44 GMT

Hello,

I have a pivot table that have two dimension ("Demand Group" and "Week") like this:

In my model, each week has an index number. For tests, I put 4 expressions:

Week: Contains the week (as in dimension) and its index
Week-2: Contains the index of 2 weeks ago (if 2011P01W3 => Week-2 = 2011P01W1 for example)

Then, in my fact table, I have the week corresponding to the date of the fact and a version (that is the week at which data were uploaded, to keep an history and compare in time).

So I can have:
Qty = 100 for FactDateWeek=2011P01W3 and Version=2011P01W3 (data for the week uploaded the same week)
Qty = 200 for FactDateWeek=2011P01W3 and Version=2011P01W1 (data for the same week but uploaded 2 weeks ago)

The "Dyn" expression should calculate the sum of Qty of "Week" with Version = Week-2 for each column of my "Week" dimension.
For this, I used the following Set Analysis and declare "Dyn" as:


=sum(
 {
 $
 <
 Version_Week_Index={'$(=only(YrPdWk_Index + 2))'}
 >
 } Qty
 )

The result is ok if I select only one Week in the Selection box

But if I display every weeks (what I need to do), I only have "0" as below:

After some research on the forum, my understanding is that I cannot use Set Analysis to loop on every column of my Week dimension. Is that correct ? (I'm new on QlikView).

Could someone help me with an idea please ?

Thanks in advance

Pascal

topic Set Analysis by dimension in a pivot table? in QlikView

Set Analysis by dimension in a pivot table?