Last value - First value for each month

Hi ,

I have excel file which has 2 columns ( Date and value). Date column contains date from 01/01/2021 till today. each date has one corresponding value.

Now I need to find the exact growth value for each month. i.e.. for Jan 2021 I need to find month end value - monthstart value( 01/31/2021 value - 01/01/2021 value). likewise for each month.

-->certain month start value or month end value might be null. so I need to ignore that as well. For example , 04/01/2021 has null value means , I need to consider 04/02/2022. likewise for month end as well

Any way to achieve this please

Developer

Accepted answers

phottovy

Hi @Karthick461 ,

Are you trying to get an individual total for each DB ID or do you only care about the overall month total? I made a change to the workflow to include several fake DB IDs. For the top part of the workflow, the main change I made is including the DB ID as one of my join fields in the join tools:

The lower part of the workflow is similar to my previous one except it now summarizes by individual dates to give a total by month across all DB IDs.

Last Value to First Value.yxmd

All comments

phottovy

Hi @Karthick461 ,

If I am interpreting your question correctly, the attached example workflow should get you close. Feel free to adjust my example data table if it is not the same as your data set and I can adjust my workflow accordingly.

Last Value to First Value.yxmd

Karthick461

Hi @phottovy ,

Thanks a lot for the logic. I have got one more column added in my input ( DB ID ). Each DB ID has date from 01/01/2021 to till today.(1st screenshot)(few DB ID's might not have few dates) . I have around 20 DB ID's.

I have modified the Workflow. In Summarize tool, I have grouped new column(DB ID) as well ( 2nd screenshot ) . In joins, i have unchecked Right DB ID. but result value is not properly coming. its not grouping via DB ID. hence change value is also not correct.

Am I missing something?

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703