Monthly Price Impact Analysis with multiple rates per customer ID

[Scarbrain]

Hello, hoping for a solution with the below

I am doing a monthly price analysis, measuring price increase and decreases by customer ID, and by line description.

However some line descriptions have more than one rate, and these rates can be vastly different, and are not always invoiced in the same period.

I would want to sum the customer ID and description by month and also sum the quantity, then divide that to get the rate per month, however these multiple rates lines cause big fluctuations in some months.

I want to create a ranking of where there are multiple rates in the month, e.g. rank 1 is lowest, rank 2 is med etc. Then I will use a range of 20% higher and lower for each ranking. That way I can categorise these rates.

I'm stuck, as I cannot figure out how to bring the ranking back to the data. 

Confusing? Sample attached.

[adamweaver39]

Hi @Scarbrain , I am confused by your explanation -- looking at your workflow, it appears you were able to bring the Rate Ranges and Ranking back into your original date source, but I think you want to include your 'Date' and 'Rate' fields group in the join condition to avoid duplicate line. Let me know if that helps!

[adamweaver39 · Accepted solution]

Hey @Scarbrain, sorry, I think I jumped the gun -- I am wondering if the workflow can be refined through using the grouping function on your Multi-Row formula. See attached workflow and let me know if those results look correct.

[Scarbrain · Accepted solution]

@adamweaver39  thanks for sending through. Although it wasn't the solution, the sort and multi-row tool was all I needed to do which you alluded to, so appreciate you having a crack!!

Here is the workflow, now I can do price increase analysis without the 3 different price points impacting the results.

[adamweaver39]

Awesome, glad you found the solution you were seeking!!