Looping Logic

[Saichebrolu]

Hello Community, i have a table below.
Need to calculate the count of Newvalue column based on Date selection.
Please observe below output exmple results for your reference.

TABLE:

Prop ID	Created Date	Old Value	New Value
ABC123	5/3/2023	Accepted	Signed
ABC123	4/29/2023	Pending	Accepted
ABC123	4/26/2023	Applied	Pending
XYZ123	4/20/2023	Accepted	Signed
XYZ123	4/17/2023	Pending	Accepted
XYZ123	4/8/2023	Applied	Pending

Expecting result example1.

User Selection:	04/15/2023 to 04/30/2023
Report Output
			Notes:
Status	Homes
Pending	0		We have two homes in our pool which are ABC123 abd XYZ123. When user selects date range 04/15/2023 to 04/30/2023 the number of homes in Pending Status are 0. Because within users date range, the latest status of ABC123 is Accepted and XYZ123 is Signed.
Accepted	1
Signed	1

Expecting result example2.

User Selection:	04/15/2023 to 05/15/2023
Report Output
			Notes:
Status	Homes
Pending	0		We have two homes in our pool which are ABC123 abd XYZ123. When user selects date range 04/15/2023 to 04/30/2023 the number of homes in Pending and Accepted Status are 0. Because within users date range, the latest status of ABC123 and XYZ123 is Signed.
Accepted	0
Signed	2

Please help me out.




Thanks
Saikumar

[Saichebrolu]

Hi Ibendlin,  
Need to count the Newvalue column Dynamically based on  Max Of createddate    in the selected daterange.

Source table:
---------------

Prop ID	Created Date	New Value
ABC123	5/3/2023	Signed
ABC123	4/29/2023	Accepted
ABC123	4/26/2023	Pending
XYZ123	4/20/2023	Signed
XYZ123	4/17/2023	Accepted
XYZ123	4/8/2023	Pending

example1:If User select above Date range
Expected Result:
Note: The count should be come under newvalue column based on dynamic maxdate.
User Selection (Slicer): 04/17/2023 to 04/20/2023 

Prop ID	Created Date	New Value	Count
ABC123	4/26/2023	Pending	1
XYZ123	4/17/2023	Accepted	1

Thanks

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[lbendlin]

There is no point in letting the user select a date range.  What you want to compute is the status of each property on the last specified date (4/30 or 5/15).

You need to consider a couple of scenarios

- there is a change event before the selected date:  Use the New value

- there is a change event after the selected date:  use the Old value

- there is no change event listed for a property:  Here you would need to use the current status of the property, which is missing from your sample data.

This pattern is very common when you deal with field history reports based on Salesforce.com data.

[Saichebrolu]

Hi Ibendlin,  
Need to count the Newvalue column Dynamically based on  Max Of createddate    in the selected daterange.

Source table:

Prop ID	Created Date	New Value
ABC123	5/3/2023	Signed
ABC123	4/29/2023	Accepted
ABC123	4/26/2023	Pending
XYZ123	4/20/2023	Signed
XYZ123	4/17/2023	Accepted
XYZ123	4/8/2023	Pending

User Selection (Slicer): 04/15/2023 to 04/30/2023 

1.If User select above Date range
O/P:

Prop ID	Created Date	New Value	Count
ABC 123	4/29/2023	Accepted	1
XYZ 123	4/20/2023	Signed	1

User Selection (Slicer): 04/17/2023 to 04/20/2023 

example2:.If User select above Date range
O/P:

Prop ID	Created Date	New Value	Count
ABC123	4/26/2023	Pending	1
XYZ123	4/17/2023	Accepted	1

Thanks