1 Reply Latest reply on Jul 22, 2016 7:05 AM by J. Nolfo

    Newbie Needs Help: Calculated fields reference based on date (Part 2)

    J. Nolfo

      Got a follow up on a question I presented last week (Newbie Needs Help: Calculated fields reference based on date ). 

       

      I have developed two different calculated fields:

       

      Promo?

      IF attr([Trans Date])>=ATTR([Promo Pricing].[Date Start])

      AND attr([Trans Date])<=ATTR([Promo Pricing].[Date End])

      THEN "Promo"

      ELSE "Non-Promo"

      END

       

      UseTotalPrice

      IF attr([Trans Date])>=ATTR([Promo Pricing].[Date Start])

      AND attr([Trans Date])<=ATTR([Promo Pricing].[Date End])

      THEN SUM([Trans Qty])*SUM([Promo Pricing].[POP Price])

      ELSE SUM([Trans Amt])

      END

       

      The data is showing up perfectly on visualizations that I have the actual dates showing, MDY(TransDate).  See screenshots below

      However, when I remove or modify the MDY(TransDate) to show aggregate levels by calendar year or by Promo field, I get bad results.  See screenshots below.

       

       

       

      I was looking into a LOD calculation, however, my transaction data source and my promo date source are two differing locations (Oracle connection & Excel).  I would not mind a calculated field that is added onto the Oracle data source, but it would be based on product number, date (multiple years), 

       

      Thoughts?  And thank you in advance for your help.

       

      J.