topic Re: Aggr-Function with different flags in one week in QlikView

Aggr-Function with different flags in one week

Milo2009 — Mon, 08 Feb 2016 17:12:01 GMT

Hey everybody,

I want to sum the sales per week and product and after that calculate the average of the sales over a period of max. 4 weeks.

This expression works fine:)

Now it can happen that a product has a flag (f.ex. X) at the weekdays MO-FR. In this case, I dont want to include the sales for this specific week in the calculation mentioned above....

Salesdate	Weeknumber	Weekday	Flag	Sales
01.02.2016	5	MO		10
02.02.2016	5	DI		10
03.02.2016	5	MI		10
04.02.2016	5	DO		10
05.02.2016	5	FR		10
06.02.2016	5	SA		10
07.02.2016	5	SO		10
08.02.2016	6	MO	X	10
09.02.2016	6	DI	X	10
10.02.2016	6	MI	X	10
11.02.2016	6	DO	X	10
12.02.2016	6	FR	X	10
13.02.2016	6	SA		10
14.02.2016	6	SO		10
15.02.2016	7	MO		30
16.02.2016	7	DI		30
17.02.2016	7	MI		30
18.02.2016	7	DO		30
19.02.2016	7	FR		30
20.02.2016	7	SA		30
21.02.2016	7	SO		30

Because some days in the week 6 have a flag, I dont want to include the whole week in my calculation. The result should be:

PRODUCZ

AVG SALES PER WEEK (last 4 weeks)

XYZ

=((7*10)+(7*30))/2 --> just the sales for week 5 and 6 and division with 2 instead of 3

Thanks a lot in advance:)

Re: Aggr-Function with different flags in one week

swuehl — Mon, 08 Feb 2016 17:34:10 GMT

Would be helpful if you posted also your current expression.

You should be able to get what you want by using a set expression that excluded weeks with a flag:

{<Weeknumber = e({<Flag = {'X'}>}) >}

Re: Aggr-Function with different flags in one week

Milo2009 — Tue, 09 Feb 2016 11:49:02 GMT

Thanks for your answer. The e/p functions were new to me:) But you never stop learning...

This is my expression for now: Sum({< Weeknumber=E({<Flag={'*'} - {' '}>}) >} (if-clauses.....) SALES)

It works, when just one product is selected. But when I select more products the result is always 0. I tried to get it working by using aggr function, but no success at all:

Sum(Aggr(Sum({< Weeknumber=E({<Flag={'*'} - {' '}>}) >} (if-clauses.....) SALES) Product,Weeknumber))

Thanks and kind regards

Re: Aggr-Function with different flags in one week

swuehl — Tue, 09 Feb 2016 14:20:25 GMT

You said that the X flag is per product. If you need to look at the current dimension line (product) then set analysis is not really easily applicable here, since it's evaluated once per chart.

You could try to generate a coomposite key for Product and Week, like

LOAD

Product,

Week,

Autonumber(Product & Week) as ProductWeek,

...

Then

Sum({< ProductWeek = E({<Flag={'*'} - {' '}>}) >} (if-clauses.....) SALES)

might work (or maybe not, since it's still quite unclear what your context and data model, if-clauses used etc. are).