3 Replies Latest reply on Oct 28, 2016 5:53 AM by . Indumon

    Aggregation or Level of Detail?

    Edward Yeldham

      I have data showing customer account number and product group purchased.  I have data for YTD sales this year (TY) and last year (LY) plus a full year LY figure.  I am determining the reasons for differences between TY and LY sales.

       

      One reason might be that the customer has purchased from us earlier than they did last year.  Below is a screen shot showing my data and the calculated field.  You can see the column totals to £214,886 on this calculated field which is correct.

       

       

      This is a lot of data and a bit unwieldy to look at.  If I take my customer off the detail shelf to summarise by the product type the calculated field doesn't behave correctly and totals to zero as shown on the shot below.

       

       

      I think it is now recalculating the field but not looking at the customer account no as this is no longer on the sheet.  Adding it to the sheet somewhere makes a real mess.


      Help please - I'm sure a simple solution!

       

      Ed

        • 1. Re: Aggregation or Level of Detail?
          Luciano Vasconcelos

          Impossible to figure out without seeing the other formulas.

          • 2. Re: Aggregation or Level of Detail?
            Edward Yeldham

            YTD SALES LY is

             

            IF DATEDIFF('year',dateadd('month',6,[Transaction Date]),DATEADD('month',6, [Select Date])) = 1

            AND DATEPART('month',dateadd('month',6,[Transaction Date]))

            <= DATEPART('month',DATEADD('month',6,[Select Date]))

            THEN [Gbp Sales Value] ELSE 0 END

             

            (select date is a user parameter and transaction date is as it suggests from the system our financial year starts on 1st July hence the 6 month field)

             

            YTD SALES TY is

            IF DATEDIFF('year',dateadd('month',6,[Transaction Date]),DATEADD('month',6, [Select Date])) = 0

            AND DATEPART('month',dateadd('month',6,[Transaction Date]))

            <= DATEPART('month',DATEADD('month',6,[Select Date]))

            THEN [Gbp Sales Value] ELSE 0 END

             

            YTD SALES FY is

            IF DATEDIFF('year',dateadd('month',6,[Transaction Date]),DATEADD('month',6, [Select Prior Year End])) = 0

            AND DATEPART('month',dateadd('month',6,[Transaction Date]))

            <= DATEPART('month',DATEADD('month',6,[Select Prior Year End]))

            THEN [Gbp Sales Value] ELSE 0 END

             

            Select prior year is parameter for the user to select the prior year end date of relevance.

             

            Not sure if these calcs are relevant?  The remaining calcs shown on my summary image are all variations of the one circled in red which doesn't work.  If I can get the one circled in red to work then I can get the rest to work.


            Ed

            • 3. Re: Aggregation or Level of Detail?
              . Indumon

              Hi Edward,

               

              LOD calculations will be an ideal solution for your problem. Please refer the attached sample solution created on superstore excel file. I used 'Fixed' expression to set the aggregation to customer level, so that its level will not change when you rollup the report level.

               

              If {Fixed [Customer ID] : ZN(Sum([YTD USD Sales TY] ))} <>0 and

              {Fixed [Customer ID] : ZN(Sum([YTD USD Sales LY] ))} =0 and

              {Fixed [Customer ID] : ZN(Sum([YTD USD Sales LY - FY] ))} <>0

              then

              {Fixed [Customer ID] : ZN(Sum([YTD USD Sales TY] ))} else 0

              End

               

              http://www.tableau.com/learn/whitepapers/understanding-lod-expressions

               

              Screen Shot 2016-10-28 at 4.44.56 PM.png