11 Replies Latest reply on Jul 19, 2018 9:09 AM by Francisco del Valle

    Calculate Total Balance - at cutoff date

    Francisco del Valle

      Hi All:

       

      I have a dataset which resembles this:

       

      Customer         Sync Date          Balance

      A                         7/16/18                    100

      A                         7/5/18                      150

      B                         7/5/18                        50

      B                         7/1/18                      100

      C                         7/14/18                    200

      C                         7/4/18                      260

      D                         7/16/18                    200

      D                         7/3/18                      300

       

      If I run the calculation to estimate the balance at a given date, I get all the Customers who've synced in that date.

      So for instance the total balance at 7/16/18 is 300. However its not that, because it should add the last sync of the other customers, so I'm looking for the total balance on 7/16/18 to add up to 550 (A=100 + B=50 + C=200 + D=200)

      So what I'm trying to do is to calculate the total balance at 7/16/18 by adding all the balances of all the customers at their last sync date prior to the date in question. Say I wanted to calculate the balance on 7/6/18 then it should total 760 (A=150+B=50+C=260+D=300).

      I've tried {fixed} with no success, and I'm guessing it should be via table calc, but have no idea where to start.

       

      Any help would be greatly appreciated.

       

       

      Ive included a small mock-up of the data.

       

      Thanks!

        • 1. Re: Calculate Total Balance - at cutoff date
          Joe Oppelt

          { FIXED : SUM( if [Sync Date] < (however you specify a cutoff date) then [Balance] END) }

          • 2. Re: Calculate Total Balance - at cutoff date
            Francisco del Valle

            Thanks Joe!

            I’d tried that approach and it doesn’t quite do the trick, because since I’m trying to make it a running line chart. So the balance is just the calculation which adds whatever synced in that date, and the balancesss is the calculation you suggested.

             

            Thanks again for taking the time.

            • 3. Re: Calculate Total Balance - at cutoff date
              Joe Oppelt

              I think I get what you need now.

               

              See attached.

              • 4. Re: Calculate Total Balance - at cutoff date
                Francisco del Valle

                Thanks!

                Couldn’t find an attachment.

                • 5. Re: Calculate Total Balance - at cutoff date
                  Francisco del Valle

                  So I tried this approach:

                   

                  if isnull(sum([balance]))

                  then PREVIOUS_VALUE(ATTR(balance))

                  ELSE

                  sum([balance])

                  END

                   

                  which gives me the desired effect, but doesn't work once I try to make it a chart which doesn't have the Customer level... It only works if I have every customer appear (which would make it too complex).

                  How could I work around this?

                  • 6. Re: Calculate Total Balance - at cutoff date
                    Joe Oppelt

                    The attachment is attached to the reply I made.  I attached it to this reply too.

                    • 7. Re: Calculate Total Balance - at cutoff date
                      Joe Oppelt

                      Francisco del Valle wrote:

                       

                      So I tried this approach:

                       

                      if isnull(sum([balance]))

                      then PREVIOUS_VALUE(ATTR(balance))

                      ELSE

                      sum([balance])

                      END

                       

                      which gives me the desired effect,...

                      If that's close to what you need, then I am still missing what you are looking for.

                      • 8. Re: Calculate Total Balance - at cutoff date
                        Francisco del Valle

                        Sorry for not being clear...

                        I'm attaching the workbook again.

                        If you look at the table sheet, you'll see the "calculation 1" field. This replicates it at the pos_id (customer) level, but it doesn't add it.

                        I would like the balances sheet to display the addition (grand total) for the "calculation 1" field.

                         

                        So to explain it a little bit more.

                        You have a customer which syncs over the cloud so you can see his balance. It might stop syncing for a couple days, but your best guess at the balance is his last synced balance. I would like to include this "last synced" balance for those days in which the customer didn't sync.

                         

                        So lets suppose you have a customer with this behavior:

                         

                        A                        7/16/18                    100

                        A                        7/8/18                      150

                        A                        7/5/18                        50

                        A                        7/1/18                      100

                         

                        The line graph i'm trying to build should include all the dates between 7/8-7/15 with the last value (the value from 7/8).

                        When you look at it from a single customer you might think that you're missing those dates from the DB, but since its many customers all dates are available from other customers. So I just want the null dates for a specific customer to include his last reported balance up to that date.

                         

                        Hope I was a little bit clearer this time.

                        • 9. Re: Calculate Total Balance - at cutoff date
                          Joe Oppelt

                          I have never liked the way Tableau handles auto-generated totals when it comes to totaling aggregates (and especially table calcs.)

                           

                          So I always (and I rarely use absolutes, but in this case "always" applies) make my own totals sheet and handle my own totals.

                           

                          (And maybe I'm getting hung up on totals because you might just want to put this value as a line on a chart rather than a total at the bottom of a crosstab.)

                           

                          On your original Table sheet I took off totals.

                           

                          See Table(2).  I created two calcs to window_sum the totals I want.  One adds the SUM(balance).  The other adds the SUM(Calculation1).  For this grid I have set the table calc setting for calculation1 to be table across.  Depending on how your actual sheet might be set up, you might select dimensions, and then do POS_ID first, and Day-of=date second, and then restart every POS_ID.  For my two new calcs, I have them set for table down.  You might need to set yours to date and then POS_ID, restarting every day of date.  So my first total gets the sum of the sum of balance for each day.  My second one gets the sum of calculation1 for each day.  Note that when you do a table calc of a table calc, you have to make sure that the nested table calcs are doing what you want them to do.  To look at that (and to reset it if needed), pull down the nested table calc list as shown:

                           

                           

                          So on sheet (2) you can see that the two table calcs do what you want them to do, but they exist for each POS_ID.  Go to sheet (3).

                           

                          On (3) I moved the two initial measures to details.  They still live on the sheet but are no longer displayed.  I created a bogus dimension to create a space holder for the same space that the POS_ID used, and then I unchecked "show header" for POS_ID.  You'll see why I did this later.  Go to Sheet 4.

                           

                          I created a calc called index.  Put it on filters.  Set it to table down, and select for value = 1.  This now displays only the first row of the crosstab.  I edited the space holder to say TOTAL.  I hid the headers, and now we have a self-defined totals sheet.  Go to dashboard.

                           

                          I popped the original Table sheet into a container, and below that I dragged the totals sheet.  I hit the title for the totals sheet.  Now things line up.  (One drawback here is the scrollbars.  They won't scroll in tandem.  Only individually.  If you really need a crosstab that long, look at dashboard 2.  There I set the width of the dashboard to 5000, and now you can scroll the whole dashboard at the bottom, and the two sheets scroll in unison.

                          • 10. Re: Calculate Total Balance - at cutoff date
                            Francisco del Valle

                            Thanks Joe... it works beautifully at the table level... sorry to bother again, but is there a way to make it work at a line chart level?

                            Since the table calc requires the customer field (pos_id) to be in the view it creates the detailed view with many lines and not easy to read, and I would like an aggregate view with just one line for all the customers.

                            I tried with sets, but didn't work.

                            • 11. Re: Calculate Total Balance - at cutoff date
                              Joe Oppelt

                              See sheet 5.

                               

                              Do the same [index] filter so you get only one line.

                              • 12. Re: Calculate Total Balance - at cutoff date
                                Francisco del Valle

                                It worked like a charm... thanks for your help!