Can you mock up what the final table looks like - I am having trouble getting there
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?)
Sorry Alex, didn't see your post before replying!
No problem - I saw the lack of a transaction ID as a problem as well..
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:
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.
Test data.xlsx 10.6 KB
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.
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.