8 Replies Latest reply on Jul 12, 2016 2:56 PM by zibal jafri

    Ignore Repeating Values while Calculation

    zibal jafri

      Hello everyone,

       

      In my data set, I have some fields which are repeated. The data set looks like as below :

       

      As you can see ; For Dan & Abdol, I have multiple records for the same date. Now when I am trying to create a calculated field 'Savings' calculated as ([Ideal Expense] - [Actual Expense]) in tableau; for the repetitive records I am not getting expected results. For example, Savings for Dan is calculated as [Ideal Expense] -  [Actual Expense] ======> [  270 (i.e 90+90+90) - 90 (i.e.30+30+30...this value is correct and is needed this way) ] = 180 whereas I want it as

      [90]-([30+30+30)] = 0, as I want only one instance of Ideal Expense ie. 90 and not sum of the three instances to be used in the calculation. The [Actual Expense] is calculated correctly by adding all the values present for multiple records.

       

      It would be great if anyone could help me with this ? I have also attached .twbx  file for reference.

       

      Thanks,

      Zibal

        • 1. Re: Ignore Repeating Values while Calculation
          Joe Oppelt

          See attached.

           

          (V9.0 workbook, will upgrade to any version you have.)

          • 2. Re: Ignore Repeating Values while Calculation
            Joe Oppelt

            I used AVG() to grab the single value for IDEAL.  I could have used MIN or MAX instead.


            What do you want to do if the three IDEALS are not the same?

            • 3. Re: Ignore Repeating Values while Calculation
              zibal jafri

              Hi Joe,

               

              Thank you for the prompt response. The IDEALS will always be the same but a record may have multiple entries for different dates as shown below:

               

               

              In this case Savings for Dan should be calculated as [Ideal Expense] -  [Actual Expense] ====> {[ 90 (for 2-jan) + 90 (3-jan) + 90(4-jan)] - [90(30+30+30....2-jan) + 90(3-jan) + (60+30....4-jan)]} = [270 - 260] = 10. But if I take the average of IDEAL, my Savings for Dan will be calculated as [90] - [260] = -170.

              Can you tell me a way to get the desired results ? Thanks in advance!

               

              - Zibal

              • 4. Re: Ignore Repeating Values while Calculation
                zibal jafri

                Please find the updated .twbx file

                • 5. Re: Ignore Repeating Values while Calculation
                  Joe Oppelt

                  (Note to self:  Working in version 9.3)

                   

                  See attached.

                   

                  It took me a while to understand what your requirement really was.

                   

                  If I understand correctly, you want the sum of the sums of the daily actual values, and only one of each day's ideal, summed across all days, calculated for each name.

                   

                  In sheet 1 I returned the DATE field to the sheet.  You're going to have to look at this data on a day-by-day basis since you need to do the IDEAL value on a day-by-day basis.

                   

                  So on Sheet 1 I have essentially the same old stuff I originally did in my first workbook.  But your description showed me that you need NAME first, and then DATE so that we cluster all the stuff for a NAME together.

                   

                  And then I created a table calc to sum up the individual cells within a NAME.  Edit [Savings per Name].  The WINDOW_SUM says to sum up aggregates.  In this case we sum the SUM(Actual), and we sum up the AVG(Ideal).

                   

                  But that's not the end of it.  We have to tell Tableau to restart the calc for each NAME.

                   

                  In the Measure Value shelf, right click on the triangle in the [Savings per Name] pill.  Choose Edit Table Calc.  In this edit box we can tell tableau to walk through the table "down" or "across" or "across then down", etc.  We can also choose Advanced.  Click on that pulldown and choose Advanced.

                   

                   

                  A new edit box will pop up.   This can be intimidating, but in here you can tell tableau which dimensions to use in walking through the table.  (Sometimes you'll have a half dozen dimensions on your sheet, and they'll all be listed here, and you may not want to use them all in addressing ...  and that's where it becomes intimidating.)  In this case you have only two, and because I know I want to tell Tableau to restart with each new NAME, I select that one first.  I also need tableau to walk through each day of data, so I select that as well.

                   

                  Click OK.  You'll go back to the earlier edit box.  Third pulldown is "Restarting Every".  That's where I selected NAME.  If I had put DATE first, that would have been my option, and I don't want that, so that's why it was important that I chose NAME first on the previous edit box.

                   

                  After picking NAME, click OK.


                  And that's how it is set up.

                   

                  But that's not the end of it.  You don't want to see the individual dates, nor do you want to see 4 rows of "Dan".  (But you'll notice that a table calc gives you the same value for all rows used in the calculation.)

                   

                  Now go to Sheet 2.

                   

                  I created another calc.  [index].  Tableau keeps track of index values.  This is a table calc too.  I put that on the FILTER shelf, and initially I just let it default to all values.  By default it does TABLE(down).  That comes up with numbers from 1 through 7 because there are 7 actual rows in this table on this sheet.  What I want Tableau to do with this is to grab just the first row of each NAME and ignore the rest.  So I edited this table calc like the other one and set it the same way as the one we did before.  Now Tableau is seeing index values that essentially count the number of rows for each NAME, and restarts with 1 with each new name.  After editing the table calc, the filter re-displays the value selection I want.  I chose min=1 and max=1.  This will grab only the first row for each NAME.

                   

                  (A special note about using a table calc in a filter.  When you use a quick filter, tableau includes into the table only those data rows that fit the filter criteria.  But when you use a TABLE calc as a filter, the underlying table still exists on your sheet, but Tableau only displays the parts of the table that fit the criteria.  If you look at Sheet 3, it's the same sheet as Sheet 2, but the table calc in the filter is set to TABLE(down) instead of the Restart-every NAME.  The whole table is there.  If you edit the filter (not the table calc) to get from min=1 through max=4, for example, you'll see that it will just get you the first 4 rows.  Just pointing that out as a point of interest on using table calcs as a filter.)

                   

                  And finally, to get the date value off the viz, I right-clicked on the DAY(Date) pill and turned off "Show Header".

                   

                  (In looking at this further, my table calc might have been OK if I just did a WINDOW_SUM([Savings per Day]).  You might play with a larger set of data to make sure that simpler logic would work.  You would still want to edit the table calc to restart every NAME if you do that.)

                  • 6. Re: Ignore Repeating Values while Calculation
                    zibal jafri

                    Thanks for your time. Yes Joe, you have understood my requirement perfectly but the file you have attached is not the updated one. That's the same .twbx file which I attached in my response. Please attach the latest file so that I can look into it for better understanding. TIA

                     

                    Regards,

                    Zibal