I’m working on transactional math. A consumer gets loyalty points from many merchants, a consumer can spend these points at many merchants. The consumer can choose 5% of their purchase at the merchant as loyalty points to spend anywhere, or 10% of the purchase to come back and spend at the same place. A point represents a dollar of buying power. I need to display the total points available for a consumer to spend at a particular merchant, and separately, the amount of points to spend anywhere.
I currently have the transactions related between the consumer and merchant, this allows me to show a history of transactions for each party. I have a math column that adds up all the transactions for each consumer to show their total wallet, but I get stuck when I want to do this addition and split by merchant which would allow me to show total points a consumer can spend back at a particular merchant.
yeah, what Jeff said.
You’ve done a decent job of explaining your challenge, but it’s still quite difficult to visualise.
A picture tells a thousand words
Tricky! I imagine you’ll first need to create an if then else column in the transactions sheet to determine if it’s a 10% in store for each line (else 0). Then, fabricate a user&merchant key value using a template column in the transactions sheet, and create an identical value in your users+merchants sheet. Then, do a multiple relation between the two. afterwards do a Rollup of that if then else column to see total bucks per store.
This will give you a total amount of Goose bucks earned per store but you’ll need to subtract any Goose bucks spent at that store as well.
I imagine if they’re spending goose bucks, then it’s a value within the transaction line. So, you’ll need to do a roll up of that column as well and subtract it from the dollars earned per store.
Of course this is all theory, you’ll have to play with it.
Oh man I’ve been going round and round in circles. you’ve outlined some fantastic ideas but i think there’s a concept I’m not getting, how to do a rollup of multiple entries in a single row, and split them by type…
doing a rollup on the transaction key (made up of the merchant and consumer email addresses) only offers total count of transactions, not per merchant?
A rollup is done on a column, not on a row.
If you’re really trying to do a rollup on a single row, it could be that you’re trying to insert a square peg in a round hole. It might be that you need to re-think how your data is organised.
One thing I would add: I did have a look at your video the other day, but didn’t respond as I didn’t really have time to study it and get my head around it. But one thing that did jump out at me is that having your buyers and merchants mixed in the same table probably makes things a bit more complicated than they otherwise could be. If it was me, I think I’d be splitting those into separate tables. I realise that some users could be in both, so there would be a little bit of duplication, but that shouldn’t create any problems.
I had originally started with them split, but because so much of the info was identical it felt smoother to put them in the same sheet - but now that i’ve got these complex rollups i think you might be right…
