How to calculate difference from previous date, for each category?

[Mehrheyr]

Hi,

I am trying to calculate difference from previous date of a variable (daily change and difference from first date) for each category in my workflow.

There are three categories in my data, Market, Delivery Month and Portfolio.

For this calculation I used The Multi-Row Formula tool (see attached file), but my calculation is wrong, can you help me on this?

Thanks,

[rfoster7]

You'd have to include a copy of yoru price change.xlsx or replace it with some sample rows in order for us to help you. 

Without that I am going to guess that your problem is that you have "delivery month" checkmarked in the Group by. I'm guessing that's your date element that changes from row to row and you mean to get "this months" price - "prior months" price. But having that checked means it acts like a partition in an aggregate function and treats each separate month as a unique item to start over. 

But if you can provide some sample data, either with packaging a version with the xlsx file, or by putting a few rows of sample data into a text input in place of the excel file, I'll try to help more. 

[Mehrheyr]

Thank you for your reply
Actually, delivery month is one of my group because I need to have difference price between date and date -1 for each portfolios, delivery month and Market. In other words I have a main group (market) with two sup-group (portfolio and delivery month):

Here is an example of this which I created with Pivot table in Excel:

[rfoster7]

I see. 

You need to do two things.

One, put a sort in before your multi row tool to sort on Market, Portfolio, Delivery Month and As of Date in order to get the data in the order it needs for the Multi Row tool to do its job in the right order

Two: Change your Price Change field to a Double in the MultiRow Tool instead of an integer, so you get the full dollar and cent differences. 

[Mehrheyr]

Perfect,
Thank you for your help
I'll check it with my real data.
Many thanks,

Mehrdad

[Mehrheyr]

Hi @rfoster7 
Thank you, I check is on my real data and that was perfect.
I have one more question, If I want to get max value or price for each date and market (same as price change) how can I do it with Multi-Row formula?
Thanks,

[rfoster7]

I wouldn't use the multi-row formula for that. I mean you could do it, but if I wanted "max price, grouped by market and delivery month", I'd use a group by tool, then append it back to the dataset like so:

But if you absolutely needed to do it with a multi row tool you probably could but I wouldn't want to figure it out