Use dimension value in set analisys

[Anonymous]

Hi,

I have that dataset as source data:

week	qty
201501	11,14
201502	10,09
201503	10,73
201504	8,41
201505	18,33
201506	14,58
201507	13,58
201508	12,62
201509	13,07

I need to develop a graph with three columns:

1. Dimension: Week
2. Expression 1: sum(qty)
3. Expression 2; avg(sum(qty)) for n periods of weeks (the number of weeks backwards)

It should be able to filter the weeks with:

1. Week Sart (Input field with variable - v.WeekStart)
2. Week End (Input field with variable - v.WeekEnd)

It should be able to indicate the value of the periods of weeks for the average ((Input field with variable - v.Period). This number of periods is open, It can be any.

I show two examples, the first one with 3 weeks backwards and the second example with 4 weeks backwards. The filter for week start and End is the same in the two examples.

Example 1

1. Week Sart = 201504
2. Week End = 201509
3. n (periods) = 3

Example 1

1. Week Sart = 201504
2. Week End = 201509
3. n (periods) = 4

Qlikview

I am able to filter the expression qty by set expression:

     sum({<Week={">=$(=v.WeekStart)<=$(=v.WeekEnd)"}>}qty)

But I dont know how to develop the second expression. I need data from more weeks than the weeks that I need to show on the graph.

How can I do it? Can you help me?

It is quite urgent please.

Thank you very much.

[Anonymous]

Hi Swuehl,

what would happen if there is no data for a day, but I want to keep that day inside the n periods?

i.e.:

• Day 20151215 but there is no data for 20151214
• n = 4
• The RangeAvg should be calculated between 20151215 and 20151212 but the attached example would calculate between 20151215 and 20151211.

Could you help me with this?

Thank you very much!!!