Join creating duplicate rows, effecting formula logic

[KittieJames]

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.

[jrlindem]

I think you could accomplish this using a Multi-Row Formula Tool (or possibly even a Running Total Tool) by computing the net remaining value based on the dates, given the price increase and then each new interval affects those same formulas.

Can you attach some dummy data and expected results?  I'm sure myself or someone would be happy to prototype out something for you if the above doesn't spark some ideas on your end.

-Jay

[bdehning]

Before joining, sort the customer table by Customer ID ascending, Nominal Code ascending , and then Price increase descending. Use the Unique Tool to keep one record for each customer's nominal code by selecting only Customer ID and Nominal Code in the columns names configuration of the Unique Tool. Then join the tables by Customer ID and Nominal Code and you can remove the remaining price increase, which is the largest.

Apologies if this is not what you wanted. Without seeing the original tables and an example of expected output I tried to infer the correct solution from the two row output you provided.

[KittieJames]

Hello,

I have attached dummy revenue data, price rise data and the desired results. 

The revenue data is the customers revenue per month per nominal code

The price rise data is the price rises applied to those nominals for each customer and the date the price rise is effective from.

The desired results show the actual revenue in column D and the price increase elements for each effective date in columns E-G.

Column H is the desired result that shows if the effective date is after to or equal to the 'month' then the price increase should be deducted from the value to show the 'pre price increase' amount. The ones highlighted in yellow show the effect of this. 

Thanks for any suggestions. 

[bdehning]

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. 

[jrlindem · Accepted solution]

@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

[bdehning]

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.