Cannibalization: Set Analysis Filter from Dimensions Values

[luisccmm]

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

.....

[sunny_talwar]

I would rather use Aggr() with if statement vs using set analysis to attack this problem. The problem is that you need different one year period based on different stores and set analysis is evaluated once per chart.

[luisccmm]

You mean using Aggr in the Script, and that is not possible to solve through set analysis?

[sunny_talwar]

No, I mean Aggr() on the front end only

[luisccmm]

stalwar1‌ I Don´t really get mean. How would you use aggr on that?

[sunny_talwar]

Would you be able to share a sample?