2 Replies Latest reply: Apr 19, 2018 10:19 AM by Luis Carmona Martínez RSS

    Aggr function not working as expected

    Luis Carmona Martínez



      I need to calculate on the clients that bought something during year 2017 in a specific store, when was the last purchased they did before that store opened 20170101.


      I was thinking to use an aggregated function to solve that, so for every client_id caculate the last INVOICE_DATE before 20170101 as

      aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID)



      And then use FUNCTION count, to count those dates (There should be just one date for every client_id in the aggregated function)


      count({<CLIENT_ID = P({<[Store_number]={"1000"}, [YEAR_INVOICE]={"2017"})*P({<[INVOICE_DATE]={"<20170101"}>)

      , [INVOICE_DATE]={"<20170101"}>} aggr( max({<[INVOICE_DATE]={"<20161123"}>}[INVOICE_DATE]) ,CLIENT_ID))

      And put that Function on a graph, in order to count those dates (that are in fact Clients_ID) with dimension INVOICE_YEAR.

      In that way I  should see how many of those clients did their previous purchase (< 20170101) in Year 2016, 2015, 2014..... In order to answer if those clients that bought in store 1000  were really active  or not.

      But that Aggr FUNCTION, is not getting the results I expect, and don´t really know why? The problem is in the Aggregated function, as seems to count Distinct Invoices Date without taking into account client_id

      aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID)

      Any Help?

      Thanks in advance!!

        • Re: Aggr function not working as expected
          David Štorek


          this expression aggr( max({<[INVOICE_DATE]={"<20170101"}>}[INVOICE_DATE]) ,CLIENT_ID) returns maximal Invoice date by client ID (so you get array of values like 20161212,20161111,20161114...).

          It seems that you are missing some brackets in expression ad there is something more than needed. Try this

          count({<CLIENT_ID = P({<[Store_number]={"1000"}, [YEAR_INVOICE]={"2017"}>})*P({<[INVOICE_DATE]={"=aggr( max({<[INVOICE_DATE]={"<20161123"}>}[INVOICE_DATE]) ,CLIENT_ID))"}>})>}CLIENT_ID)

          Hope it helps

            • Re: Aggr function not working as expected
              Luis Carmona Martínez

              Thanks David, but there is some misspealling in your formula, that I don´t really get.


              On the other hand, I think I more or less understand what you are trying as seems that your looking for the Joint between these two sets:

              - Clients ID for that particular shop.

              - Maximum Dates per client ID prior That StoreOpen Day.

              Nevertheless, this will probably lead to an ERROR, because if some clientes BOUGHT in different Dates along different YEARS, and some of others dates match the MAX Date of other client (2nd Set) that will count those client more than one time in different years. I am right?