Same Store Analysis

[Amit_B]

Hi everyone,

I need your help with building a pivot table for a same-store analysis.
I looking for a UI-based solution, no script changes if possible.

The comparison needs to be done according to the opening and closing dates of each store (active store received current date as closing date).

Dimensions: MonthID (dim 1 row), DayNumber (dim 2 row), Region (dim 1 column), StoreNum (dim 2 column).
Measures:
* Max Year Sales (by selection)
* Prev Year Sales (Max - 1)
* % between max & prev
(There are variables that define the max year and the prev year)

Examples:

Store 100 is an active store that was opened on July 25, 2022.
* If I compare between 2025 (max) and 2024 (prev), I will get data from January 1 until current date. Any data in 2024 after the current date (September 15) is irrelevant.
* If I compare between 2024 (max) and 2023 (prev), I will get data for the entire year because the store was active in both years.
* If I compare between 2023 (max) and 2022 (prev), I will get data for each year starting from July 25 until December 31. Sale between January 1 2023 and July 24 2023 will not be considered because the store was not active during that period in 2022.

Store 200 was opened on April 2 2024 and closed on June 28 2025.
For this store we can only compare data when the max year is 2025, and the comparison should only include the period between April 2 and June 28, which is when the store was active in both years.

Store 300 was opened and closed in the same year, so it cannot be included in the comparison at all.

This logic should be applied per store, so the user can compare at the store level, region level or overall.

I hope I was clear enough.

Thanks!

[Amit_B]

Just to mention that the easiest way for me was to try solving it with Aggr(), but I got stuck with it.

At first, I calculated based on whether there were sales on the same day,
but that ignored holidays and Sundays when the stores are usually closed, so this is an incorrect calculation.

This is what I tried to do:

For Max Year Sale:

Sum(
     Aggr(
          If(Sum({<Year={$(vMaxYear)}>}Sales) <> 0  and Sum({<Year={$(vPrevYear)}>}Sales) <> 0,
               Sum({<Year={$(vMaxYear)}>}Sales) <> 0
          ),
          StoreNum, MonthID, DayNumber
     )
)

[marcus_sommer]

I think you will need several nested if-loops querying the open/close dates against the selected years respectively which periods are valid and should be included within the comparison - related to the store-level and wrapped with some aggr() to aggregate the results to higher level.

It's not very complicated by starting with a single condition for a single year and then adding step by step all further conditions to fetch all possible cases. 

[QFabian]

Hi @Amit_B , since my case of SSS is about customers making buys 12 months in a row, for me was significantly a better option do it in script, with all necessary fields for grouping , and with that all selections are respected and apllied.

[Amit_B]

This is a problem for me.
There are many fields  that need to be included in the grouping, so that would make a long load time for the model.

[Amit_B]

I tried that but I think I didn't define it properly so it didn't work.

Do you have an example?

[marcus_sommer]

I couldn't provide an example. Like above hinted the general logic is probably quite simple - you need just n (more or less combined/nested) conditions - developed step by step. Not taking several jumps at once else things like:

sum(Field)

if(condition1, sum(Field)

if(condition2, sum(Field)

if(condition3, sum(Field)

...

in n parallel expressions - to see if the conditions are working like expected, then combining/nesting some and checking the results again and then so on ... rates, aggr() and further things do come later.

[marcus_sommer]

It's not mandatory necessary to pre-calculate everything within the script but the UI logic might be significantly simplified with some data-model adjustments, maybe in the way to create flags which are checking the opening/close dates against daynumberofyear() and/or in relation of having complete calendar-years or similar measurements. 

[Amit_B · Accepted solution]

Thanks for the help.
Eventually, I created a table linking stores and dates, and set flags:
* Is the store open on that day (use for MaxYearSales & PrevYearSales).
* Is the store was opened on that day last year (use for PrevYearSales).
* Is the store will be open on that day next year (use for MaxYearSales).