David -- Trying to juggle all the facts of your description without a hands-on example to work with makes my head spin.
Can you provide a sample workbook? I'd like to take a look.
attached you find a sample workbook. I hope it provides most of the information you need,
Again, what I want to show is a weekly based sales comparison that considers only the stores, that where open during this specific week.
All the best,
Let me understand a little more about what you have here.
Is it sufficient to say that if a store's open date is >= week-1 of 2015, and if that store's closing date is <= week-1 of 2016, then (and only then) we should include it in the sheet? (And I see nulls in Closing Date in the data. We can take that into account as well. If null than we assume it's not closed.)
Basically what I think you're looking to do is to filter into the sheet only stores that were open during a specific range of time. (And if I'm not mistaken, they have to be open the complete range of that time.)
Is there ever a chance that a store could close and then reopen within the set of data? (Or for any given store, it will have only one open date and only one closed date throughout the data.)
The calculation I used is only valid for the first week in 2016.
If the store's open date is <= 01.01.2016, than tableau should include the store because it was open in the first week 2015 as well as in the first week 2016.
If the store's closing date is null, it should also be included (under the condition that the opening date is <= 01.01.2016)
If the the store's closing date is <= 01.01.2015, it should also be included. If the closing date would be > 04.01.2015, we would take into account sales data from a store that was open in the first week 2015 but is closed in the first week 2016.
What I want to compare is an equal number of stores for every week of the year. (The stores that were open in the first week in 2015 as well as in the first week in 2016, the stores that were open in the second week in 2015 as well as in the second week in 2016 and so on)
There is no chance that a store could close and reopen within the data set.
I still don't understand what you are aiming for.
See the attached. Point out some specific examples of what you would want included and excluded. Are you looking to evaluate this on a week-by-week basis?
For instance, see "10206 Paris". It closed in week 9. Would you want Paris included, but only weeks 1 through 9? And Munich would not be included at all?
(Note: I modified [DateCLo] to replace "null" with TODAY() so that we have an actual date to look at for this exercise.)
Like4Like_Test A.twbx 73.7 KB
Exactly, in your example, Paris should only be included for the weeks 1 through 9.
Munich, Milan, Prague, Barcelona and Madrid shouldn't be inlcuded at all.
Köln should only be considered for the weeks 27 to 52, because it opened in week 27 in 2015.
Yes, I want to evaluate this on a week-by-week basis. Every week should only consider the stores that were open in this specific week in both years, 2015 and 2016.
Thanks a lot!
I added two new calcs to Sheet 2. One is to evaluate whether we should keep that data based on the opening, and one is based on the closing.
In sheet 3 I added a filter calc. If both evaluations are "Keep" then grab it.
In the end, you don't need to display all that intermediate stuff. I just left it on there to show what's going on.
Like4Like_Test B.twbx 100.5 KB