Join creating duplicate rows, effecting formula logic

Question

Hi All,

I am trying to deduct price increase amounts from a value.

I have 2 datasets, one for customers revenue per nominal code per month for the year and another for price increases applied to each customer and nominal code throughout the year and I have joined the 2 sets together based on customer (site id) and nominal code.0.

I then planned to do a formula to say if the effective price rise date is greater than or equal to the revenue month then deduct the price increase from the customers revenue, as shown below.

The issue I have is that some customers have had more than 1 price rise on the same nominal throughout the year, so when I do the join it is creating 2 rows as seen below. The below is showing the customer has been charged £1,268.27 twice in March 2025 when they are actually only charged once. I want there to only be 1 line for this data and to be able to deduct the price increase amount of £63.48 from the value each month from Feb 25 onwards and in addition deduct £8.04 from the value from Apr 25 onwards.

Thanks in advance.

bdehning · Answer

I like this solution better than mine. I need to use the running total tool more instead of always relying on the multi-row formula tool. I also wondered about the need for the monthly price increases in their own columns, but added a cross tab tool anyway to match the desired output. Using the fixed decimal data type also removes the need for rounding at the end that I had to do to get an exact match with the desired results.

jrlindem · Answer

@KittieJames - I've attached a workflow using the Running Total Tool to show how this can be done.  Now, I admit that I did not follow listing out each price increase in it's own column.  Over time, that can get quite unmanageable.  I chose to allow the data to remain vertical, which is more optimal for larger datasets, and I would encourage you to consider this for your solution.  Then, once you've filtered down to the relevant time period you can pivot the data out to match your desired output format.

Here's my example workflow that will scale to whatever data you throw at it, so long as it follows the same format.  It regards the price change, only when it occurs and then resets for the next Nominal, etc.

I've also attached the workflow.

Nice job, @bdehning on your solution.  I reviewed it and agree with your disclaimers.

Hope these two solutions give you guidance and inspiration.  Reach out with any questions, -Jay

jrlindem_example.yxmd

bdehning · Answer

Attached is a solution. It isn't elegant, but it works (sort of). There appear to be three customers in the test data missing from the desired results. I had to filter out 40110, 40410, and 40540 to get it to work. @jrlindem 's solution of a running total for the discount worked. I'd be happy to look at my solution if you want me to test it with 40110, 40410, and 40540 included in the desired results.

I also tried to make the workflow adaptable for future periods and more sites. if you have more expansive data I can also test that. However, you should be able to attach your own data and results files and test the workflow for a larger dataset. Just go into the Select Tools and make sure you choose all of the fields that you would like to display.

KittieJames Solution.yxzp