How to calculate no. of records carried forward for each months before the request gets closed

[pranaview]

Hi Guys,

Requirement: So I get the data everyday which contains requests closed the day before and currently open requests in the system. Now what the user wants to see is the amount of requests that are being carried to next month(s).Let's take an example below

A requests gets opened in the month of May and gets closed in the Month of July which means request is currently in open state.So what i am doing currently is checking whether the Month Opened < Current Month (May < Jun) and hence it's a carried forward request which is totally fine, now when we move into July and the request gets closed so now my condition changes to May < July which is again true and Status remains Carried Forward. Now when i am showing carried forward requests for July month, it will show correctly but when i'll show the carried forward request into Jun month, this request will get missed since the record is now closed and updated in July month so there's no trace of it in Jun month data. 

Hope i made some sense, please let me know if you get the use case and how can i implement this logic

Thanks!

[vamsee · Accepted solution]

If I got your scenario right, in the given use case the RequestID does not have any record for June.

I would suggest you populate data for all the requests at a month level. like

Request ID	Month	Status
1	May	Open
1	Jun	Open (To populate this refer below)
1	Jul	Closed
1	Aug	Closed

You can do this using:  sparsely populated field. 

Instead of Rate, use status and you don't have to generate at date level Month Level should work fine.

[Saravanan_Desingh]

Can you show the sample and expected output?