3 Replies Latest reply: Feb 9, 2016 9:20 AM by Stefan Wühl RSS

    Aggr-Function with different flags in one week

    Sebastian Müller

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

         

      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 advance:)

        • Re: Aggr-Function with different flags in one week
          Stefan Wühl

          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
              Sebastian Müller

              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
                  Stefan Wühl

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