your cart

How to add up hierachy target by using nested LOD calculation

    In previous blog, I talked about "How to calculate hierarchy target by using LOD calculation". Today I will talk a little deeper and it can be expand to other place.

    Suppose your boss setted a target for category for each month. He can easily compare sales and target in each month. But if at the begining of March he want compare total sales of Jan + Feb and total target of Jan + Feb. How to do it?

    For demonstration, I will create a target only for California in Jan and Feb.

    First calculated field is Target:

IF [State]='California' AND YEAR([Order Date])=2017 THEN

CASE [Category] 

WHEN 'Furniture' THEN

CASE MONTH([Order Date])

WHEN 1 THEN 900

WHEN 2 THEN 1200

END

WHEN 'Office Supplies' THEN

CASE MONTH([Order Date])

WHEN 1 THEN 3000

WHEN 2 THEN 3200

END

WHEN 'Technology' THEN

CASE MONTH([Order Date])

WHEN 1 THEN 1000

WHEN 2 THEN 2000

END

END

END

    Second calculated field is LOD Target:

{FIXED [Category]: SUM({FIXED MONTH([Order Date]),[Category]:AVG([Target])})}

    Inner LOD tell tableau to calculate target per month and per category, outter LOD tell tableau not to consider month, so it will add up month target untill current month which in our example are Jan and Feb.

   Then we drag State, Order Date to filter to only keep California and Jan, Feb of 2017. Year and Category to row and Measure name to column and keep Sales, Target and LOD Target as below image:

    If you remove MONTH(Order Date), you will see Target column will give you a wrong number, but LOD Target is right.


    Now we need create another calculated filed to show only one correct target based on you choose year to date target or month target.

    YTD Target or Month Target:

IF COUNTD(MONTH([Order Date]))>1 THEN AVG([LOD Target])

ELSE AVG([Target])

END

    Then drag it to Measure Values, and you will see below:


    When there are 2 months, it equals Target, when month is removed it equals LOD Target.




Blog Category