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

    LY Comp calculation troubles

    Nichil Kantelal

      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.