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

    Two years difference and multiply by row wise

    Kiran K

      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 NameSelected year Metric 1 (A)Comparative year Metric 1 (B)Selected Year Units (C)

      Output

      (A-B)*C

      A20.530.33-29.4
      B14220.43-3.44
      C 1801203.4204
      D72.562.7549
      Total220.16

       

       

      Thanks !

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

          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
            Kiran K

            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
              Shinichiro Murakami

              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

                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
                201716 {OFF TRADE DIRECTOR, IRELAND}0019,563.150
                201757 {ES OFF TRADE DIRECTOR}000.157890
                201787 {OFF TRADE DIRECTOR, GREAT BRITAIN}0031,090.330
                2017181 {OFF TRADE DIRECTOR, GERMANY}0000
                2017367 {OFF TRADE (L2)_GB}0053,648.790
                2017GTME0000
                2017GTME-AIRLINES0000
                2017GTME-CRUISE0000
                2017GTME-FERRIES0000
                2017GTME-TRAVEL RETAIL0000
                2017UNASSIGNED0000
                201816 {OFF TRADE DIRECTOR, IRELAND}756,078115.71087603139.938706
                201887 {OFF TRADE DIRECTOR, GREAT BRITAIN}0000
                2018367 {OFF TRADE (L2)_GB}134,88026,976044960
                Total48099.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
                201716 {OFF TRADE DIRECTOR, IRELAND}0019,563.150
                201757 {ES OFF TRADE DIRECTOR}000.157890
                201787 {OFF TRADE DIRECTOR, GREAT BRITAIN}0031,090.330
                2017181 {OFF TRADE DIRECTOR, GERMANY}0000
                2017367 {OFF TRADE (L2)_GB}0053,648.790
                2017GTME0000
                2017GTME-AIRLINES0000
                2017GTME-CRUISE0000
                2017GTME-FERRIES0000
                2017GTME-TRAVEL RETAIL0000
                2017UNASSIGNED0000
                201816 {OFF TRADE DIRECTOR, IRELAND}756,078115.71087619,563.15-14,703,779,707.59
                201887 {OFF TRADE DIRECTOR, GREAT BRITAIN}0031,090.330
                2018367 {OFF TRADE (L2)_GB}134,88026,97653,648.793,638,522,880
                Total-11065256828
                • 5. Re: Two years difference and multiply by row wise
                  Shinichiro Murakami

                  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

                    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

                      [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

                        **** 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
                        16 {OFF TRADE DIRECTOR, IRELAND}756,078115.71087619,563.15-14,703,779,707.59
                        87 {OFF TRADE DIRECTOR, GREAT BRITAIN}0031,090.330
                        367 {OFF TRADE (L2)_GB}134,88026,97653,648.79-3,597,625,762.25
                        Total-18301405470
                        • 9. Re: Two years difference and multiply by row wise
                          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.