6 Replies Latest reply on Apr 20, 2017 9:11 AM by pradhyumna kulkarni

    I need your help in Tableau calculation.

    pradhyumna kulkarni

      I need your help in Tableau calculation. How do I get my first value into Month1 and second Value into Month2 ..

       

      Below is the screenshot

       

      Current:

       

      Current.png

       

       

      Expected:

       

      Expected.png

       

      Thanks,

      Prady

        • 1. Re: I need your help in Tableau calculation.
          Okechukwu Ossai

          Hi Prady,

           

          How is your data structured? You can achieve this by using the LOOKUP function. However, the form of the code will depend on how your data is structured.

           

          I've mocked up some data assuming the measure is only 1 field called [Value].

           

          Create a calculated field [Value new]

          IF ATTR(MONTH([Created Date])) = 3 AND ATTR(YEAR([Created Date])) = 2015 THEN LOOKUP(SUM([Value]),1)

          ELSE SUM([Value])

          END

           

          Hope this helps.

          Ossai

           

           

          • 2. Re: I need your help in Tableau calculation.
            Nalini Goli

            Hi Ossai,

             

            This logic is working only when you have a Null value for the combination of  Created Date = 03/01/2015 and Transaction Date = 02/01/2015.

             

            Thanks

            • 3. Re: I need your help in Tableau calculation.
              Okechukwu Ossai

              Hi Nalini,

               

              It works when you have a Null value or a blank. In both cases the row already exists in the data source. The way Tableau works is that you can add as many columns as you wish within Tableau. However, you cannot add a new row within Tableau. All required rows must pre-exist within the data source, whether Null, empty or otherwise, before you can overwrite or add anything to it.

               

              Tableau normally needs an existing "row container" before it can move something into it. When you have Null values or a blank it means the row already exists in the database, so it is possible to modify that record. I assumed that this is the case with Prady's question.

               

              The problem begins when the target row doesn't exist in the data source, Tableau will most likely say no, for the right reasons, when you ask it to move a value into "nothing". In contrast, a column doesn't need to pre-exist. You can add as many columns as you wish within Tableau. This is possible because columns are added to existing rows. So, you can make a data source fat in Tableau but you can't make it tall.

               

              It will be good if you can attach a sample dataset when this solution failed to work and I will help you take a look.

               

              Ossai

              • 4. Re: I need your help in Tableau calculation.
                pradhyumna kulkarni

                Hi Ossai,

                 

                Thanks for the solution.

                 

                But in my case I have millions of rows and do not have null or blank values for these combinations.

                 

                I have Created Date for more than 24 months and when we compared with Incident Created Date there are more Null or Blank scenarios.

                 

                Attached is my workbook as well. Could you please help me in achieving the same result with the attached workbook.

                 

                Thanks,

                Prady

                • 5. Re: I need your help in Tableau calculation.
                  Okechukwu Ossai

                  Hi Prady,

                   

                  Please see my response to Nalini above. This won't work if the records are non-existent for those date combinations. We are asking Tableau to move a value into a row which doesn't exist. It will definitely struggle to do that. Even the flexible Microsoft Excel cannot do this without manually inserting those records.

                   

                  So, I'll suggest inserting all missing rows into your database, leaving [Value] as null. I'm not sure if this option is viable for you or not but If you really need this, then consider it. For example, in your database, you need Created Date = 01/03/2015, Transaction Date = 01/02/2015 and Value =    .

                  Do this for all the missing created and transaction date combinations. Depending on your data model, you can do this by directly inserting the missing records to your table, or through a join.

                   

                  Hope this helps.

                  Ossai

                  • 6. Re: I need your help in Tableau calculation.
                    pradhyumna kulkarni

                    Hi,

                     

                    This is the right solution for the issue

                     

                    Step 1: Just use the formula “Index()” then format Month Number to include a month prefix

                    Sol1.png

                     

                     

                    Step 2: Drag MONTH(Transaction Date) from Columns to Detail

                     

                    Step 3: Drag [Month Number] from Measures pane onto Columns

                     

                    Step 4: Right-click [Month Number] on Columns and set Compute Using to Transaction Date

                    sol2.png

                     


                    Thanks,

                    Prady