2 Replies Latest reply on Nov 6, 2018 12:49 PM by Kyle Peterman

    Trying to use LOD calc to get to a single days value

    Kyle Peterman

      I'm sure this should be easier than what I'm making this out to be, but I will outline what I'm running into and hopefully someone can help.


      I have a dataset which has two dates, I'm trying to isolate all the values for a single date so that I can use that value as a denominator for a later calculation.


      I have created two date fields which isolate each day that I'm looking at:


      (I'm using Superstore data for the example and filtering it to only the Max(order date) and MIN(order date) to try to represent my dataset)


      Day_A Calendar Day = {FIXED: MAX([Order Date])}

      Day_B Calendar Day = {FIXED: MIN([Order Date])}


      I was trying to do a simple LOD calc to return only the sales when the "Order Date" field equals the Day_A Calendar Day field:


      Total Sales-Day_A = {FIXED [Order Date] = [Day_A Calendar Date] : SUM([Sales])}


      What I thought would happen is that the "Total Sales-Day_A" field would only contain Day_A Sales values and all other days would be null but that doesn't seem to be what's happening.  What seems to be happen is that is treats the calc like it is this:

      {FIXED [Order Date] : SUM([Sales])}


      The problem is that I'm using this calculation as the denominator in a percent difference calculation where I am calculating the a percentage change on Day_A, a percentage change on Day_B, and subtracting the two percentages from each other all in a single calculated field, so the denominators must be using only the single day's total sales values.


      Note:  Some of the calculations look like they may have extra criteria (i.e. certain segments evaluations), but that is because my real dataset have these criteria, so I am trying to keep my calculated fields as close to the real dataset as possible.


      What I am expecting is the following:


      Day_A Values (Dec 30, 2018)

      Sales for Consumer Segment: $470

      Total Sales for all Segments: $714

      Day_A Percent of Total: 65.83%


      Day_B Values (January 3, 2015)

      Sales for Consumer Segment: $16

      Total Sales for all Segments: $16

      Day_B Percent of Total: 100.00%


      Difference = (65.83% - 100.00%) = -34.17%



      Any help would be much appreciated