Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

sebmueller87
New Contributor II

Aggr-Function with different flags in one week

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 fineSmiley Happy

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....

   

SalesdateWeeknumberWeekdayFlagSales
01.02.20165MO10
02.02.20165DI10
03.02.20165MI10
04.02.20165DO10
05.02.20165FR10
06.02.20165SA10
07.02.20165SO10
08.02.20166MOX10
09.02.20166DIX10
10.02.20166MIX10
11.02.20166DOX10
12.02.20166FRX10
13.02.20166SA10
14.02.20166SO10
15.02.20167MO30
16.02.20167DI30
17.02.20167MI30
18.02.20167DO30
19.02.20167FR30
20.02.20167SA30
21.02.20167SO30

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 advanceSmiley Happy

Tags (1)
3 Replies
MVP
MVP

Re: Aggr-Function with different flags in one week

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'}>}) >}

sebmueller87
New Contributor II

Re: Aggr-Function with different flags in one week

Thanks for your answer. The e/p functions were new to meSmiley Happy 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

MVP
MVP

Re: Aggr-Function with different flags in one week

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).