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.

[sunny_talwar]

See if the attached application helps:

Expression for Avg = =If(Sum(qty) > 0, RangeAvg(Above(Sum({<week>}qty), 0, $(vVar))))

[Anonymous]

Thank you very much Sunny.

I am working with this version:

and it doesnt work. Although I select some weeks, all the weeks are shown (see the atached image):

I have tried it with v 11.2 and it works fine.

Do you know something about this? Is it an issue?

Do you know another way to solve the problem?

Thanks for all.

[sunny_talwar]

It seems like a problem with =sum(qty), not sure why it is still staying even after a selection have been made in week field. I guess one way would be:

If(Sum(qty) > 0, Sum(qty))

but the problem here would be that if there are selected weeks with 0 Sum(qty), those will also go away.

swuehl‌ do you have a clue of why this might be happening in v11.0? Was there some kind of bug back then?

Thanks,

Sunny

[swuehl]

Try if this works in 11.0:

=Aggr( RangeAvg(Above(Sum({<week>}qty), 0, $(vVar))),week)

edit: If this works in 11.0, you still may need to take care that week field values are showing a chronologic load order (like in your sample data set), because aggr() dimension values are sorted by load order.

[swuehl]

Sunny, I think 'supress zero values' now works again, while it was automatically disabled in former versions when using chart inter record functions (though the warning is still in my version of the HELP file).

[Anonymous]

Thank you very much. It has been very useful.

It works very well with aggr.

But I have another problem.

I have attached a document and an excel with the data for the model of the document.

In the first post, I wrote that what I needed to calculate was sum(qty) but it´s a little more complicated.

My data model is that one:

The graph would looks like that:

so:

1. Dimension: Time. It´s a group (Gr.Tiempo) with the Year (Año), Month (Mes), Week(Semana) and Day (Dia). The user will be able to show the data to the desired level. I know what level I´m showing by the variable vL.NivelTiempo = GetCurrentField([Gr.Tiempo])
2. Ratio:
   • 
     
   • SUM of PC/PVP by Reference (field Referencia) * % Weighing (the percent of PC that represents the Reference to the total of PC of all the References selecected)
   • count(Tienda): count different values of field Tienda
3. Avg: The RangeAvg of 'n' periods (the vL.Periodo variable allows to modify the number of periods)
4. St.Dev: The RangeStdev of 'n' periods (the vL.Periodo variable allows to modify the number of periods)
   

THE PROBLEM

To make it easier, we'll do it at the level of day.

Values for the example:

• The number of periods will be 7 (vL.Periodo = 7).
• Referencia = REFERENCIA 4, REFERENCIA 5
• Cadena = CADENA 1

The calculation of Ratio is working well.

But when I select some values of Days (field Dia), returns the value of Ratio ok, but not the values of AVG and ST.DEV.

It looks like its making the calculation only with the days that I have selected, so the AVG returns the same value as Ratio, and the St.Desv doesnt work.

For example, in the left image all the data, in the right image some values of day selected (20151209, 20151210, 20151211)

My expressions are:

1. Ratio:
2. Avg:
3. St.Dev:

I do not know if I explained well, its not easy.

Can someone help me? I think the problem is the aggr but I dont know añother way to calculate it.

Thank you.

[swuehl · Accepted solution]

Maybe like attached

[Anonymous]

Hi Swuehl,

You are the best!!!

I also need to develop the same graph but:

1. As Dimension I will have the Day and the Field Cadena
2. As Expressions I will have:
   1. Ratio and Avg (by Day and Candena)
   2. Ratio by Day (without Cadena, all selected Cadenas)

The idea is compare two Cadenas.

Thank you very much.

[Anonymous]

Hi,

I think that I have resolved!!!

Thank you!!