7 Replies Latest reply on Oct 10, 2013 7:53 AM by Dan Thompson

    Can I show dollar amounts and counts in tier by day

    Dan Thompson


      Hello,

       

      Here is what I am trying to accomplish... I have consumers who spend money every day of the month. I would like to break up that spending (count and amount) by tiers. Thinking of it this way, if you spend $500 in a day total then I want to see it broken down by only transactions (singluarly or combined as needed) equal to or less than $100.01 in the first tier; followed by the next $100 worth of spending but less than $200.01 total spending in the second tier; followed by the next $100 worth of spending but less than $300.01, etc. all the way up to $500.01 in total daily spending.

       

      Here is an explanation of what I am after using the data in the attached:

       

      Acct 582, makes 6 purchases on Aug 1, totalling $280.11 - consisting of $4.90, 18.51, 50, 50, 56.70, and 100.  Accordingly, a table would show 1 transaction totalling $100 in the first BIN or Tier, $100 in the second BIN or tier (2 trans of $50 each), $80.11 in the third and final BIN or Tier. The total of all BINs or Tiers is equal the $280.11 of spending for the Aug 1.

       

      Note - I would like the option of changing the range of the spending Tiers or BINs. 

       

      I have created a BIN, but not sure it is the right approach. It appears to be looking at each transaction by itself and then sticking it in the appropriate BIN rather than considering the total spending for the day and slotting the next bit of spending based upon the range of each BIN.

       

      I hope you can help me. Much thanks in advance.

       

      Dan

        • 1. Re: Can I show dollar amounts and counts in tier by day
          Alex Kerin

          Can you mock up what the final table looks like - I am having trouble getting there

          • 2. Re: Can I show dollar amounts and counts in tier by day
            kevin.lynch

            Hi Dan,

             

            I had a quick look to see if I could help out. The first thing that jumps out is that without a Transaction_ID of some kind, it will be very hard to manipulate the view, specifically because you are trying to bin individual transactions.

             

            I presume this is a dummied set of data, or a stripped down version? If you could either include the native transaction_id or dummy one to match what you will have, and re-upload it'd be great.

             

            Also, when connecting to the workbook, if you connect live instead of importing the data, and then upload. That way I'll be able to unpackage the workbook and manipulate the excel sheet if necessary (presuming you have the potential to control what the source data should like?)

             

            Cheers,
            Kevin

            • 3. Re: Can I show dollar amounts and counts in tier by day
              kevin.lynch

              Sorry Alex, didn't see your post before replying!

              • 4. Re: Can I show dollar amounts and counts in tier by day
                Alex Kerin

                No problem - I saw the lack of a transaction ID as a problem as well..

                • 5. Re: Re: Can I show dollar amounts and counts in tier by day
                  Dan Thompson

                  Thanks Kevin.

                   

                  Unfortunately, I dont have a unique transaction ID assigned to each purchase. I have a few other data elements not shown in the mock up, but they may or may not be unique to the purchase (they could repeat).

                   

                  I might be able to simplify it further. So instead of siloing each transaction into several $100-incremental BINs, I think I could get away with maybe setting up two BINs.

                   

                  Here is a more real world explanation of what I want to do. Let's say you and Alex both have a credit card, and you both use it daily. I want to be able to show a total number of dollars (and count) that you and Alex complete in a given day up to a maximum of $500 (this limit may change) with your respective credit cards. So, now I can show that you may have only spent an amount in that day that fell under $500 cumulatively - for example purposes we'll say you spent $300 total for the day). Alex on the other hand tried to use is card for multiple purchases or whatever, but in any event one of more of his purchases pushed him over the daily spending limit of $500 - again for example purposes we'll say Alex spent $600 in the same day. I want to show the dollars that Alex was successfully able to use the card for (<= $500) and dollars that Alex was declined for when using his card that day ($100) - again, assuming a $500 daily spending limit. This data would be aggregated for all cardholders in the database.

                   

                  Report would show:

                  Day 1

                  Total spending below limit all cardholders: $1000

                  Total spending above limit all cardholders: $100

                  Transaction count below limit all cardholders: X (whatever number of card uses per cardholder as long as the total spending was under $500 per cardholder)

                  Transaction count above limit all cardholders: X (whatever number of card uses per cardholder that exceeded the total spending limit of $500, and would have been declined)

                   

                  Here is the question I am trying to answer... If I set a spending limit of X dollars per day based on current spending, how many cardholders, transactions and dollars would be impacted by exceeding the X dollars daily limit?

                   

                  I may be making this more complicated, but honestly my brain is fried right about now from trying different ideas to get what I need.

                  Thank you.

                  • 6. Re: Re: Re: Can I show dollar amounts and counts in tier by day
                    kevin.lynch

                    Hi Dan,

                     

                    As far as I can see, with the data you have you wont be able to get Tableau to give you the report you want.

                     

                    The reasoning is that you need to use a Table Calculation to give you a Running Sum per day, per Account, then use that Table Calc to create a Boolean flag saying if it's under or over the limit. And then you want to count what's in each bucket.

                     

                    BUT, you can't aggregate a table calc... or use it in a filter, or do anything that will get you the result you want (as far as I can find). I've spent a good bit of time trying to get around this issue with clients, but in the end I always end up going back to the structure of the data.

                     

                    I've attached two files, an adjusted excel sheet with calcs to give you an idea of what you'd need to do in your data source and a .twbx with the only solution I can see before changing the data source, and then the solution with the included calcs.

                     

                    To note, in your sample data you don't have a timestamp, just a date. Which stops you being able to calculate which transactions are pushing the account over the daily limit. I added dummy time info to your dates to fix this. If time isn't included in your data then you'll have a bit of a problem...

                     

                    If you do have time in your data, then you can create a Daily_Transaction_Number by indexing on transactions per account per day.

                     

                    I've artificially created a Transaction_ID. If you were storing this in a DB then you could just auto increment a primary key field and that'd do the trick. Each row is a transaction already.

                     

                    Definitely open to improvements / corrections on this by anyone else, but I'm pretty sure it's all sound.

                     

                    Hopefully this is helpful, any questions let me know and I'll do my best to advise further.

                    • 7. Re: Can I show dollar amounts and counts in tier by day
                      Dan Thompson


                      Thank you Kevin! I really appreciate your time. I will definitely see what I can do about going back to the data and determine if I can pull in additional elements that can help.

                       

                      Dan