2 Replies Latest reply on Jun 30, 2016 10:32 AM by Robert May

    YTD YoY Calculation Help - Why Does This Formula Not Calculate Ratios

    Robert May

      I have found a few ways to calculate YTD and compare to the same timeframe of the previous year using a reference date parameter.

       

      I used what I found to recreate a YoY YTD comparison down to the day and it is working great, with one issue.  This formula does not calculate multipled/divided measures.  It will add/subtract measures just fine.  Is there a reason for this?

       

       

      Here is my calculation:

       

      if DATEDIFF('year',[Date],[Reference Date])=1
          and DATEPART('dayofyear',[Date])<DATEPART('dayofyear',[Reference Date])

      then [Clicks]

      ELSEIF DATEDIFF('year',[Date],[Reference Date])=0
          and DATEPART('dayofyear',[Date])<=DATEPART('dayofyear',[Reference Date])
        

      Then [Clicks]

      END

       

       

       

      and I used that for 3 measures - Clicks, Impressions, and Click Through Rate (Clicks/Impressions)

       

      The CTR calculation looks the same:

       

      if DATEDIFF('year',[Date],[Reference Date])=1

          and DATEPART('dayofyear',[Date])<DATEPART('dayofyear',[Reference Date])   

      Then ([Clicks]/[Impressions])

      ELSEIF DATEDIFF('year',[Date],[Reference Date])=0

          and DATEPART('dayofyear',[Date])<=DATEPART('dayofyear',[Reference Date])

      Then ([Clicks]/[Impressions])

      END

       

      Here is a screen shot:

       

       

      Any clue why multiplication/division does not work?  Is there a fix for this?

       

       

      Thank you