3 Replies Latest reply on Aug 18, 2016 7:07 AM by Bill W

    Previous vs Previous Of Previous Month Comparison

    Bill W

      Evening,

       

      I've checked the forums it didn't appear anyone is completely a comparison when the values are in different rows rather just returning the value.

       

      So, essentially what I am trying to do is compare the previous month values against the previous of the previous month and compare them against themselves based on a single dimension then either give it a "Thumbs Up" or Thumbs Down" based on if the value from the previous of previous month is greater than the previous month.

       

      Calc 1 - [Previous Month Amount]

      If datepart('month',[Date Field]) = datepart('month',dateadd('month',-1,Today()))

      then [Amount]

      else null

      End

       

      Calc 2 - [Previous of Previous Month Amount]

      If datepart('month',[Date Field]) = datepart('month',dateadd('month',-2,Today()))

      then [Amount]

      else null

      End

       

      Calc 3 (Comparing them) - [Previous Month and Previous of Previous Month Camparison]

      If [Previous of Previous Month Amount] > [Previous Month Amount] then

      "Down" else "Up" End

       

      However, once I put Calc 3 to the view everything shows as up.

      It should show

      Team A - Down

      Team B - Down

      Team C - Up

      Team D - Up

       

      Any thoughts? Was thinking maybe an LOD, but couldn't figure out how to do that.

      Also, please advise this was built in 9.2, I can't use 9.3 or 10 version yet.

       

      Thanks in advance for any ideas.

       

      - Bill