5 Replies Latest reply: Apr 17, 2018 12:03 PM by Sunny Talwar RSS

    Cannibalization: Set Analysis Filter from Dimensions Values

    Luis Carmona Martínez

      I have a list of 200 stores, each of them has been openend at different dates, and need to calculate the SUM(SALES) for each of those on the 1st year with this restrictions:

       

      - Sum of sales from customers who have bought something in other stores before the OpenStoreday

       

      I can easily calculate that just by one store, what I don´t know how it to calculate that in 1 measure for all of them.

       

      Let´s say that Store Number 1 Opened on 20171212 (Year, Month, DaY) so STORE_OPENDAY value is 20171212 as well

       

      CANIBALIZATION_VALUE

      Sum({<

      CLIENT_ID = P({<[INVOICE_DATE]={"<20161212"}>})

      *P({<[STORE_NUMBER]={"1"},=>})

      >}[SALES])

       

      How should this formula be change in order that INVOICE_DATE could be calculated in a table, depending on the STORE_OPENDAY of each store.

       

      STORE_CODE       OPENDAY          CANIBALIZATION_VALUE

      1                               ...                     ...

      2                               ...                      ...

      .....