9 Replies Latest reply on Oct 6, 2018 12:54 PM by Kiran K

# Two years difference and multiply by row wise

Hello Team,

Want to calculate difference between two years and multiply with current year measure by row wise. I have tried using Lookup function to get last year data, but unable to do it because it's a table calculation output is aggregated. With LOD INCLUDE function, again difference is aggregated . Have any idea, how to do it. Here is an example.

 Customer Name Selected year Metric 1 (A) Comparative year Metric 1 (B) Selected Year Units (C) Output(A-B)*C A 20.5 30.3 3 -29.4 B 14 22 0.43 -3.44 C 180 120 3.4 204 D 72.5 62.7 5 49 Total 220.16

Thanks !

• ###### 1. Re: Two years difference and multiply by row wise

Difficult to understand the row data and calculated value.

Could you please attach mocked-up sample data as twbx format.

Thanks,

Shin

• ###### 2. Re: Two years difference and multiply by row wise

Hello Shin !

Here is sample twbx file (V 10.3.1). In sheet 3, comparing the data between 2017 and 2018 data. Here I want the difference from 2018 and 2017 data and multiply with 2018 row level.  In Rate field have used the calculation for output. Please let me know if you need any details.

Thanks !

• ###### 3. Re: Two years difference and multiply by row wise

Cold you specify the attached book's field name with your example of A,B and C.

Thanks,

Shin

• ###### 4. Re: Two years difference and multiply by row wise

Looks like I have confused here, sorry for that. Here is the sample table.

Rate  = ((#CALC: Anchor VPMC Metric 4 Per Depletion) - (#CALC: Comparator VPMC Metric 4 Per Depletion)) * #CALC: Anchor Depletions

View data from Tableau : Total is 48099.938706 (wrong)

 Year of CalendarDate {{Customer Level 1}} #CALC: Anchor Depletions (C) #CALC: Anchor VPMC Metric 4 Per Depletion (A) #CALC: Comparator VPMC Metric 4 Per Depletion (B) Rate (A-B)*C 2017 16 {OFF TRADE DIRECTOR, IRELAND} 0 0 19,563.15 0 2017 57 {ES OFF TRADE DIRECTOR} 0 0 0.15789 0 2017 87 {OFF TRADE DIRECTOR, GREAT BRITAIN} 0 0 31,090.33 0 2017 181 {OFF TRADE DIRECTOR, GERMANY} 0 0 0 0 2017 367 {OFF TRADE (L2)_GB} 0 0 53,648.79 0 2017 GTME 0 0 0 0 2017 GTME-AIRLINES 0 0 0 0 2017 GTME-CRUISE 0 0 0 0 2017 GTME-FERRIES 0 0 0 0 2017 GTME-TRAVEL RETAIL 0 0 0 0 2017 UNASSIGNED 0 0 0 0 2018 16 {OFF TRADE DIRECTOR, IRELAND} 756,078 115.710876 0 3139.938706 2018 87 {OFF TRADE DIRECTOR, GREAT BRITAIN} 0 0 0 0 2018 367 {OFF TRADE (L2)_GB} 134,880 26,976 0 44960 Total 48099.938706

Expected Total -11065256828: row level difference (A-B) any multiply with C (here copied 2017 data in 2018 for B)

 Year of CalendarDate {{Customer Level 1}} #CALC: Anchor Depletions (C) #CALC: Anchor VPMC Metric 4 Per Depletion (A) #CALC: Comparator VPMC Metric 4 Per Depletion (B) Rate (A-B)*C 2017 16 {OFF TRADE DIRECTOR, IRELAND} 0 0 19,563.15 0 2017 57 {ES OFF TRADE DIRECTOR} 0 0 0.15789 0 2017 87 {OFF TRADE DIRECTOR, GREAT BRITAIN} 0 0 31,090.33 0 2017 181 {OFF TRADE DIRECTOR, GERMANY} 0 0 0 0 2017 367 {OFF TRADE (L2)_GB} 0 0 53,648.79 0 2017 GTME 0 0 0 0 2017 GTME-AIRLINES 0 0 0 0 2017 GTME-CRUISE 0 0 0 0 2017 GTME-FERRIES 0 0 0 0 2017 GTME-TRAVEL RETAIL 0 0 0 0 2017 UNASSIGNED 0 0 0 0 2018 16 {OFF TRADE DIRECTOR, IRELAND} 756,078 115.710876 19,563.15 -14,703,779,707.59 2018 87 {OFF TRADE DIRECTOR, GREAT BRITAIN} 0 0 31,090.33 0 2018 367 {OFF TRADE (L2)_GB} 134,880 26,976 53,648.79 3,638,522,880 Total -11065256828
• ###### 5. Re: Two years difference and multiply by row wise

You have already created too much complicated formula and unfortunately this is beyond my threshold which I can support as forum activity.

Thanks

Shin

• ###### 6. Re: Two years difference and multiply by row wise

Thanks Shin !

I have been trying from couple of days but couldn't get desired results. Very much appreciated anyone help on this.

• ###### 7. Re: Two years difference and multiply by row wise

[Kiran had reached out to me through email]

I’m sorry, you’re going to have to prepare either a simpler example or have more explicit steps. I can’t tell from the second post whether the copied values from 2017 are supposed to be there (i.e. we need pad out the data) or you were just doing them as a demo. Also I can’t tell whether the -11 billion Total result you refer to is supposed to be a single number in a view, show as a grand total, or what?

My suggestion is to:

1) prepare a demo data set that matches your data in structure (this might be the existing data that you used, I'm not clear on that).

2) prepare the steps as you expect to see them *using the demo data set*

3) including the expected results as you expect to see them in the final view *using the demo data set*.

The reason why I have to ask for more specificity is that like Shin I have limited time to help and the complexity of the underlying data & existing calculations is such that it's all to easy to try to build the calculation you need and have the numbers go wrong because I don't fully understand all the underlying details.

Regards,

Jonathan

• ###### 8. Re: Two years difference and multiply by row wise

**** Updated expected number

Hi Jonathan !

I have copied 2017 data for demo and put in tabular format to understand better. Here looking for total value as -18 billion , want to use this calculation on Test sheet for Rate measure (others as well).

In test sheet, I have added "Rate "calculation on tooltip (please hover on Rate bar) to show aggregated value for testing but eventually will use in original calculations.

Here, want to get difference from two years and multiply with 2018 depletions based on customer Level (row level), value should be -18B instead of 48K  (wrong because current calculation is aggregating at year level and performing the calculation. Here want to perform the calculation first and aggregate at end.

Attaching Tableau workbook (V 10.3.1)

{{Customer Level 1}}#CALC: Anchor Depletions 2018 (C)#CALC: Anchor VPMC Metric 4 Per Depletion 2018 (A)#CALC: Comparator VPMC Metric 4 Per Depletion 2017 (B)Rate  (A-B)*C
87 {OFF TRADE DIRECTOR, GREAT BRITAIN}0031,090.330