5 Replies Latest reply on Jul 22, 2018 8:30 AM by Aravinda Kumar

Comparing two different hierarchy data in row fields

How can I create a calculated field to compare data in the row fields with a different hierarchy level, sample below.

Basically, I have two data sets, Spends is Spend type and Sales is by Total.

Spend TypeCategoryData TypeSpend ValueSales Value
ATLHair OilsSpends100
BTLShampoosSpends50
ATLHair OilsSpends40
BTLShampoosSpends75
Hair OilsSales120
ShampoosSales90
• 1. Re: Comparing two different hierarchy data in row fields

Depends on what you want to do with the data...

To get totals for each category combination you can use a LOD expression.

calc1   {FIXED [Spend type],[Category],[Data Type]: Sum([Spend Value])}

calc2   {FIXED [Spend type],[Category],[Data Type]: Sum([Sales Value])}

if data type is only SALES and SPENDS then you could also use this to get the difference of sales and spend.

{FIXED [Spend type],[Category]: Sum([Sales Value]) - Sum([Spend Value]) }

G

• 2. Re: Comparing two different hierarchy data in row fields

These calculations are not working, basically, I want 210 to be repeated in 2nd and 3rd row in Calculation 2 column.

• 3. Re: Comparing two different hierarchy data in row fields

Couple of modifications to the original formulas.

First you have to force tableau to put values in the Sale Value for ATL and BTL.

Calc 1:  zn([Sales Value])

This is the same formula as #1 noted above

Calc 2:  {FIXED [Spend Type],[Category],[Data Type]: Sum([Spend Value])}

This summarizes the Sales Value to the ATL/BTL types.

Calc 3:  {FIXED [Category]: Sum([Calculation1])}

You could also do it in one calculation:

{FIXED [Category]: Sum(zn([Sales Value]))}

• 4. Re: Comparing two different hierarchy data in row fields

Thank you so much Garth, it solved my problem.

The only issue I have now is, This calculation is not adding for the category which is not in the database in ATL or BTL.

For Example:

I have 3rd category in Sales called Conditioners, but not in ATL and BTL.