
1. Re: Two years difference and multiply by row wise
Shinichiro Murakami Oct 2, 2018 3:36 PM (in response to Kiran K)Difficult to understand the row data and calculated value.
Could you please attach mockedup sample data as twbx format.
Thanks,
Shin

2. Re: Two years difference and multiply by row wise
Kiran K Oct 2, 2018 4:58 PM (in response to Shinichiro Murakami)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 !

Test Forum.twbx 3.6 MB


3. Re: Two years difference and multiply by row wise
Shinichiro Murakami Oct 2, 2018 5:31 PM (in response to Kiran K)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
Kiran K Oct 2, 2018 6:39 PM (in response to Shinichiro Murakami)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 (AB)*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 GTMEAIRLINES 0 0 0 0 2017 GTMECRUISE 0 0 0 0 2017 GTMEFERRIES 0 0 0 0 2017 GTMETRAVEL 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 (AB) 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 (AB)*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 GTMEAIRLINES 0 0 0 0 2017 GTMECRUISE 0 0 0 0 2017 GTMEFERRIES 0 0 0 0 2017 GTMETRAVEL 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
Shinichiro Murakami Oct 2, 2018 6:48 PM (in response to Kiran K)You have already created too much complicated formula and unfortunately this is beyond my threshold which I can support as forum activity.
Sorry about that.
Thanks
Shin

6. Re: Two years difference and multiply by row wise
Kiran K Oct 2, 2018 7:29 PM (in response to Shinichiro Murakami)Thanks Shin !
I have been trying from couple of days but couldn't get desired results. Very much appreciated anyone help on this.
Thanks in advance !

7. Re: Two years difference and multiply by row wise
Jonathan Drummey Oct 3, 2018 9:23 AM (in response to Kiran K)[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
Kiran K Oct 3, 2018 3:26 PM (in response to Jonathan Drummey)**** 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 (AB)*C 16 {OFF TRADE DIRECTOR, IRELAND} 756,078 115.710876 19,563.15 14,703,779,707.59 87 {OFF TRADE DIRECTOR, GREAT BRITAIN} 0 0 31,090.33 0 367 {OFF TRADE (L2)_GB} 134,880 26,976 53,648.79 3,597,625,762.25 Total 18301405470 
Test Forum.twbx 3.5 MB


9. Re: Two years difference and multiply by row wise
Kiran K Oct 6, 2018 12:54 PM (in response to Kiran K)Hi,
I have used LOD Fixed on customer level and added contex to it, got expected results.
Thank you you all for your time and help.