Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Join us in Bucharest on Sept 18th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis by dimension in a pivot table?

Hello,

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

error loading image

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

error loading image

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

error loading image

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

0 Replies