Forum Discussion
Looping Logic
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
4 Replies
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.
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
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/2023Prop ID Created Date New Value Count ABC123 4/26/2023 Pending 1 XYZ123 4/17/2023 Accepted 1
ThanksPlease provide sanitized sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.
