8 Replies Latest reply on Mar 15, 2018 11:54 AM by Abhishek Singh

    How to find percent difference of data  from the previous COLUMN data

    Abhishek Singh

      Hi,

       

      I am stuck I want to find the %difference from the previous column data and also I am applying filter on the months

       

      Here is the snapshot! I want to find the last 2 columns and the first 6 columns are the data.

       

      7/20178/20179/201710/201711/201712/2017Diff from Last MonthDiff from 3Mth Avg
      41483536305,16317110%15236%
      411012111611,006525%1540%
      28,86830,9381,6782,1252,803139,046773%1011%
      3764934224583611,350274%226%
      2402083393088141,52487%213%
      1382262604354601,046127%172%

       

      I also want a filter in which I can select a month and year and it will show the difference in % relative to that selected Month year. Ex: If i select feb 2017 then it should give difference from jan and feb data.

       

       

       

       

      I had attached the sample twbx file

      Thanks in advance.

        • 1. Re: How to find percent difference of data  from the previous COLUMN data
          Esther Aller

          Hi Abhishek,

           

          You might start by looking at the options in Calculating Difference Between Two Values of the Same Measure in a View | Tableau Software

           

          If neither of those options work, it would be easier to give a more specific answer if you were able to share a sample workbook that uses sample data with the same structure as your original data. The sample data does not have to be very big.

           

          Hope this helps!

          • 2. Re: How to find percent difference of data  from the previous COLUMN data
            Abhishek Singh

            Hi Esther Aller,

             

            I have attached the sample twbx in the original post. i tried lookup and all but its not working properly. I dont want to show the difference  of every month with respect to its previous month.
            What I want is if i select Jan 2018 then i should get the column named diff last month which calculates the % of difference from last month i.e from dec 2017 and i can sort it on descending order.  In a similar manner  I also wants to see the difference from last 3 months average numbers. i.e (average of oct nov and dec ) compared with jan 2018 as jan 2018 is selected in filtering option.

             

            Let me know if you need any more information from my side.

             

            Thanks,
            Abhishek

            • 3. Re: How to find percent difference of data  from the previous COLUMN data
              Joydip Thakur

              Hi Abhishek,

               

              You can calculate the fields as follows -

               

              1. diff from last month :

               

              (LOOKUP(SUM([Number of Records]),LAST()) -LOOKUP(SUM([Number of Records]),LAST()-1))/LOOKUP(SUM([Number of Records]),LAST()-1)

               

              2. Diff From Last 3 Month Avg :

               

              (

              LOOKUP(SUM([Number of Records]),LAST())

              -

              ((LOOKUP(SUM([Number of Records]),LAST()-1) + LOOKUP(SUM([Number of Records]),LAST()-2) + LOOKUP(SUM([Number of Records]),LAST()-3))/3)

              )

              /

              (((LOOKUP(SUM([Number of Records]),LAST()-1) + LOOKUP(SUM([Number of Records]),LAST()-2) + LOOKUP(SUM([Number of Records]),LAST()-3))/3))

               

              I'm attaching the solved workbook also.

               

              Please let me know if that works!

               

              (LOOKUP(SUM([Number of Records]),LAST()) -LOOKUP(SUM([Number of Records]),LAST()-1))/LOOKUP(SUM([Number of Records]),LAST()-1)

              • 4. Re: How to find percent difference of data  from the previous COLUMN data
                Abhishek Singh

                Thanks Joydip Thakur for immediate reply.

                 

                The solution seems to work fine but I need more flexibility, right now if I select DEC 2017 then it will show data till DEC 2017 and the difference columns.

                 

                My questions are:
                1) Is there any way to hide those monthly columns or is there way in which i have to select only one month and it automatically shows all the data till that date as now i am selecting all months of 2017 till Dec I want if i select only Dec 2017 then it should show all the data in cross-tab with those 2 newly created fields.

                 

                2) Is there a way to sort the company list by newly created field as I am trying to sort but its taking aggregate value so its seems to be difficult to sort in an order.

                 

                Thanks once again for your quick reply

                 

                Regards,
                Abhishek Singh

                • 5. Re: How to find percent difference of data  from the previous COLUMN data
                  Ivan Young

                  Hi,

                  So are looking for something like below?

                   

                  • 6. Re: How to find percent difference of data  from the previous COLUMN data
                    Abhishek Singh

                    yes I am looking for something like this.
                    but I need the filter to select the month and year. So lets consider I am selecting Jan 2017 then the difference from last month should be [number of records]  in jan 2017- [number of records] in December 2016. Similarly is with the case of 3 months ave difference. Basically I want to select a month and year and i want to get the result in the difference columns with respect to that selected month and year.

                     

                    In the below image if the month selection is 5 which is may and year is 2017 the i am getting both the difference

                     

                    but if i do month feb or march 32018 then difference from 3 months average is null as its not getting the data from 2017 year

                     

                    • 7. Re: How to find percent difference of data  from the previous COLUMN data
                      Ivan Young

                      Is there a reason you need to break out month and year separately?  It doesn't really affect my solution, however, in my experience users tend to prefer less clicks to get to the data they desire.

                       

                      If you want the underlying data for your table calculations to be unfiltered you need to use a table calculation as a filter.  In my example I created a custom date, Date (Months).

                       

                      1. Create a custom date, Date (Months).

                       

                       

                      2. Create a table calc based on your custom date.

                      3. You will then need to place the new field on filters and set compute to table across.

                       

                      If you want to keep your date broken out into month and year numeric values you will follow the same steps, but you'll need to build 2 table calcs, one for month and another for year.  Let me know if you have any questions.


                      Regards,

                      Ivan

                       

                      PS, your table calc filters will need to be discrete rather than continuous.  If you have a problem with tableau defaulting to continuous, first drag your table calc to rows, then drag it from rows to filters. 

                      1 of 1 people found this helpful
                      • 8. Re: How to find percent difference of data  from the previous COLUMN data
                        Abhishek Singh

                        Hi Ivan,

                         

                        Is it possible to share the Twbx file. I tried your way but when i select dec 2017 then also its giving the same result. Where I am lacking?

                         

                        Thanks,
                        Abhishek Singh