1 Reply Latest reply on Nov 16, 2016 5:27 PM by diego.medrano

    Difficulty to understand Date Comparison Calculations in Tableau

    jiaying.huang

      http://paintingwithdata.tumblr.com/post/110252562002/date-comparison-calculations-in-tableau

      I am reading this author(Date Comparison Calculations in Tableau ) technique to set the LM, YTD, L3M, LTM's formula for the Monthly filter  and I have the below questions.

      1. the logic to set LM/YTD  and L3M should be similar, Why the author only gets the remainder of the number after dividing 12 for L3M (Highlighting in blue) and has different formula for LM and YTD(Highlighting in orange)

      ?--Why the L3M need to divide 12 and get the remainder and the other two did not divide 12?

      2. For the LTM's selection, does the author assume all the data should bring in so that she set Else number is 1. Why LTM does not have the similar formula against the other 3?

       

       

      Monthly Filter Formula

      CASE [Period]

      //when the selected period is LM, if the month of the order date is equal to the month of

      //the parameter, then keep the row, otherwise filter it out

      when 1 then IIF(month([Order Date]) = month([Order Date (Months) Parameter])-1,1, 0)

      //when the selected period is YTD, if the month is less than the month of the parameter,

      //then keep it, otherwise filter it out

      when 2 then IIF(month([Order Date]) < month([Order Date (Months) Parameter])+1, 1, 0)

      //when the selected period is L3M, if the remainder when dividing the difference in months

      //by 12 is 0, 1, or 2, then keep the row, otherwise filter it out

      when 3 then IIF([Difference in months]%12 <3 and [Difference in months]%12 >-1,1, 0)

      //when the selected period is L12M, if the remainder when dividing the difference in months

      //by 12 is between or equal to 1 and 11, then keep the row, otherwise filter it out

      else 1

      end

       

      I would love to ask for the expert to help me understand the author's logic and thanks in advance