Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Sum Query

Highlighted
Atom

Hi, I have been struggling with the concept for weeks, I hope you can help.

 

I have 2 x input tables (please see attached simplified data sample) 1 x table for transactions and 1 x price table.

 

In theory I am trying to replicate the last table. (which would be displayed (grouped) on one row) just showing the where a deal has taken place.

 

I need to sum all the [Price] fields from the price table where it matches the first [Trans_Date] (01/01/2019) from the trans_table and stop when another transaction after the date happens (07/01/2019) and so on for every new [Trans_Date]

 

shown in red and purple.

 

 

Highlighted
Castor
Castor

Hi @cantley1 

 

You forgot the attachment.

 

Dan

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner

@cantley1,

 

My buddy @danilang must be busy, so I am assisting him in giving you a potential solution.  I fear that this might be a complicated approach, but here's a way to get there.

 

First I create a record for every transaction date from first to last transaction.  Then setup data for a join to the price table.  Using the running total, I summarize the prices and join that data back.  Finally, I do some cleanup.

 

capture.jpg

 

If nothing else, this should get you going.

 

Cheers,

 

Mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Highlighted
Atom

Thank you ever so much!! This is fantastic Mark, it's exactly what I needed. I can not thank you enough. Nicky.

Highlighted
Alteryx Certified Partner
Alteryx Certified Partner
My pleasure. It was more involved than I initially thought it would be. A little puzzle to warm up with.

Cheers,

Mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and reboot. Order shall return.
Labels