2 Replies Latest reply on Jul 24, 2019 1:55 PM by Nichil Kantelal

# LY Comp calculation troubles

Hey 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 non-comp for even one day of that time period, it should be excluded from those comp calculations. for example this store (above) would be non-comp 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.

• ###### 1. Re: LY Comp calculation troubles

Can you please show the exact scenario where it's not working?

• ###### 2. Re: LY Comp calculation troubles

well the method i try to utilize only works for current year, but you cannot do last year because the data is filtered for the period defined by the parameters.

For example this store would be comp for 1/1/2017 to 1/7/2017 but if you take ly for that selection it wouldnt be comp. 1/1/2016 to 1/7/2016 contains a non comp flag so the whole selection is non comp and thus must be excluded from non-comp sales for LY 