7 Replies Latest reply on Jul 12, 2016 12:13 PM by Aiswarya Sundaram

    Newbie Needs Help: Calculated fields reference based on date

    J. Nolfo

      I am new to Tableau and I am sure this is an easy question to some of the more advanced within the community here.  However, I am seeming to have trouble.  I have check the forums and feel like I am getting close to an answer, but not 100% there.

      I have an Oracle data source that is bringing in transaction information that shows pricing at MSRP.  I also have a separate Excel file with pricing on products during a promotional period (each product had a different period).

       

      I have a data source filter set up on bringing 2 months of sales for 2015 and 2016, but the promo time frame was different for each product in each year.  There are nearly 500,000 lines of data when this pulled in.  Additionally, I have linked the products in the Oracle data source and the Excel file.

       

      What I am trying to do is develop a new calculated field within the data source view called "UseTotalPrice" that I would be doing some analysis on.  I feel like, based on what I can figure out, I may need to make a few different intermediate steps to get what I eventually need.

       

      Here is what I have done so far with nothing really working.

       

      New Calculated Field

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

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

      THEN (ATTR([Trans Qty])*ATTR([Promo Pricing].[POP Discount Per Case]))

      ELSE ATTR([Trans Amt])

      END

       

      This results in "null" in every record.

       

      Then I read about LODs and put { } around it and I get an error "All fields in a level of detail expression must come from the same datasource".  So I figured I bring in the Date Start and Date End into the Source with this calculated fields.

       

      IF ATTR([Calendar Year])= '2016'

      THEN ATTR([Promo Pricing].[Date Start])

      END

       

      This results in "null" in every record. 

       

      Based on this, I think it is not recognizing the product link, but not sure and do not know where to go from here.

       

      Thoughts?  And thank you in advance fro your help.