8 Replies Latest reply: Mar 23, 2013 10:43 AM by Alessandro Pisano RSS

    aggregations on the dates do not work as I would like to

    Alessandro Pisano

      Hi,

      I have a data mart that freezes the sales situation every day.

      For example, this is the content of the facts of the customer "Vitrociset Spa" (I left out the other dimensions are not interesting here):

      DT_ANALISI_PORTAFOGLIO_KEYV_EURO_PORTAFOGLIO_LORDO
      413541
      413541410
      413541700
      413543000
      413546900
      413548400
      413549312,2
      4135410031,62
      4135423367,9
      4135428990,22
      4135433078
      4135433301,6
      4135433806
      4135530000
      4132020000
      41323220000

      (Note that the date is expressed in numeric format)

      a simple pivot for customer / date with the total sales gives this result

      ClienteDT_ANALISI_PORTAFOGLIO_KEYDT_ANALISI_PORTAFOGLIO=SUM(V_EURO_PORTAFOGLIO_LORDO)
      Vitrociset Spa4135421/03/2013193.299
      Vitrociset Spa4135522/03/201330.000
      Vitrociset Spa4132015/02/201320.000
      Vitrociset Spa4132318/02/2013220.000

      (for clarity I also added the date in date-format)


      I would make the total sales per customer / month, using only data from the last day of the relevant month in a selected year.

      I selected the year 2013.
      I have available the dimension DT_ANALISI_PORTAFOGLIO_MESE (month of the date).

       

      in a pivot for customer / month I calculated the expression MAX_DT_ON_MONTH as

      =Max({DT_ANALISI_PORTAFOGLIO_MESE} DT_ANALISI_PORTAFOGLIO_KEY)

      and I get the correct result

      ClienteDT_ANALISI_PORTAFOGLIO_MESEMAX_DT_ON_MONTH
      Vitrociset Spamar41355
      Vitrociset Spafeb41323

       

      I applied this espression in the aggregate expression on the value V_EURO_PORTAFOGLIO_LORDO) as

       

      =sum({$ <DT_ANALISI_PORTAFOGLIO_KEY ={$(=Max({DT_ANALISI_PORTAFOGLIO_MESE} DT_ANALISI_PORTAFOGLIO_KEY))}>} V_EURO_PORTAFOGLIO_LORDO)

       

      But I can not get what I want. This is the result:

      ClienteDT_ANALISI_PORTAFOGLIO_MESEMAX_DT_ON_MONTHPORTAFOGLIO_MAX_DT_ON_MONTH
      Vitrociset Spamar4135530000
      Vitrociset Spafeb413230

       

      The first value is correct (30.000); but the second value is missing: the value on "feb" is 220.000 (the sum of the rows with DT_ANALISI_PORTAFOGLIO_KEY equal to 41323).

       

      What am I doing wrong?