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

# Aggregation or Level of Detail?

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?

Impossible to figure out without seeing the other formulas.

• ###### 2. Re: Aggregation or Level of Detail?

YTD SALES LY is

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

THEN [Gbp Sales Value] ELSE 0 END

YTD SALES FY is

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?

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