LY Comp calculation troubles
Nichil Kantelal Jul 24, 2019 11:34 AMHey all! Thank you all for taking the time to read.
I currently a set of data that looks like the following:
The task at hand is as follows: If a user selects a date, lets say 1/1/2017 to 1/7/2017, i need to figure out what are the comp sales for that date. Here is the kick, if a store was noncomp for even one day of that time period, it should be excluded from those comp calculations.
for example this store (above) would be noncomp for based on the user date selection, and all of the sales of this store should be excluded from comp sales.
the above selection would be comp, and comp sales should include all sales from this store.
I tried to resolve this problem by creating a new column, using the calculated fields:
Comp Bolean>
If [Comp/Non Comp]="Comp" THEN 0 ELSE 1 END
This was to create a columns of 0 and 1s, after that I created a calculated field:
Fixed Sum>
{ FIXED [Store Number]:SUM([Comp Bolean])}
This would do the sum for every store, giving me a 0 or non 0 value.
Then I created a date filter
Date T/F>
If [Date]>=[Start Date] and [Date]<=[End Date] THEN "T" ELSE "F" END
Then I added the date filter into context and only left values with "T"
then I added Fixed Sum to filter and only left 0 values.
What this did was left only stores that are comp throughout the time period selection. So I successfully was able to do comp sales for this year.
However, this method cannot be applied for a previous period whether that is last year, last week, last month due to the order of operations in tableau, even if i create a calculated field to do last year comp sales. Tableau does the fixed calculations before the date calculations hence I am not able to do it.
If anyone is able to help me calculate a previous period comp sales that would be awesome.
I have attached work book with the work i have done thus far.

comnon comp.twbx 26.2 KB