Same Store Analysis

[fishmanl]

Hi all

I've already made a a comparison revenue sheet with 2 alternate states - Period 1 an Period 2 (year & months)

Now there is a request to add same store filter that will filter only stores that were opened in both of the alternate states periods and where opened during the time frame, which is indicated in the following table:

Branch, Open Date, Close Date

Basically in order the branch will be marked as "same store" it needs to be open in that time frame that i choose as period 1 and period 2

1. I've started by creating the following table which have list of all the dates per Branch between the opening and closing 

NoConcatenate
SS_Branches_tmp:
Load
INDEX_Branch,
date(OpenStoreDate,'DD/MM/YYYY') as SS_Branches_OpenDate,
if(year(date(CloseStoreDate,'DD/MM/YYYY'))=1899,date(today(),'DD/MM/YYYY'),date(CloseStoreDate,'DD/MM/YYYY')) as SS_Branches_CloseDate_tmp
resident Branches
order by INDEX_Branch DESC
;

NoConcatenate
SaleDates_tmp:
load Distinct
INDEX_Branch as INDEX_Branch,
Date_Key as SS_Branches_Date
resident KeyTable;
left Join
SS_Branches:
load
INDEX_Branch,
date(SS_Branches_OpenDate+ IterNo()-1 ,'DD/MM/YYYY') as SS_Branches_Date,
'SS' as SS_Branches_Type
resident SS_Branches_tmp
while SS_Branches_OpenDate+ IterNo()-1 <= SS_Branches_CloseDate_tmp;
;

NoConcatenate
SaleDates:
load
INDEX_Branch&'|'&SS_Branches_Date as INDEX_SSDates,
INDEX_Branch as SS_Branches_Branch,
SS_Branches_Date,
if(len(SS_Branches_Type)>0,'SS','NOT SS') as SS_Branches_Type,
if(len(SS_Branches_Type)>0,1,0) as SS_Branches_TypeNum
resident SaleDates_tmp;

drop table SaleDates_tmp;
Drop Table SS_Branches_tmp;

2. Then i've created the following filter in the UI - basicaly its counting the working days in each selected period (Period 2) and counting for each branch it's SS (Same store) days the days between it's opening and closing 

if the working dates are greater than the SS its not Same store

aggr(
if(
COUNT( TOTAL {1<Year=Period2::Year,Month=Period2::Month,Week=Period2::Week,Date=Period2::Date,INDEX_Branch=>} Date)
>
AGGR(COUNT(DISTINCT {<Year=Period2::Year,Month=Period2::Month,Week=Period2::Week,Date=Period2::Date,SS_Branches_Type={'SS'}>} SS_Branches_Date),Branch)
,'Not SS','SS'),Branch)

now, the problem is that it's now filtering all the data in the sheet - only if i put it into pivot table and use the above filter as a dimension with "not to include null value"

please help me to implement it so it will filter properly

BR

Lev

[Qrishna]

sample data, current result and needed result please?