Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

luisccmm
Contributor

Cannibalization: Set Analysis Filter from Dimensions Values

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

.....

5 Replies

Re: Cannibalization: Set Analysis Filter from Dimensions Values

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
Contributor

Re: Cannibalization: Set Analysis Filter from Dimensions Values

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

Re: Cannibalization: Set Analysis Filter from Dimensions Values

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

luisccmm
Contributor

Re: Cannibalization: Set Analysis Filter from Dimensions Values

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

Re: Cannibalization: Set Analysis Filter from Dimensions Values

Would you be able to share a sample?

Community Browser