Column Query Works sometimes but not for each row.

[quincy_p]

Hello - I have recently written a query to caulcate service dates for a number of products, they should be serviced anually or twice per year depending on the frequency, within a year of the Start Date.

However, I noticed that not all data is populating despite there seemingly being nothing wrong with the code and, in some cases, the information does populate. 

Here are the codes: 

PM1 = IF (
    ISBLANK ( PM_date_kpo[SVMXC__Start_Date__c].[Date] ),
    BLANK (),
    SWITCH (
        VALUE(PM_date_kpo[PM_Interval]), -- Convert to Integer using VALUE function
        6, DATEADD ( PM_date_kpo[SVMXC__Start_Date__c].[Date], 6, MONTH )-1,
        12, DATEADD ( PM_date_kpo[SVMXC__Start_Date__c].[Date], 1, YEAR )-1,
        PM_date_kpo[SVMXC__Start_Date__c].[Date]
    )
)

And then:

PM2 = 
IF (
    ISBLANK ( PM_date_kpo[PM1].[Date] ),
    BLANK (),
    SWITCH (
        VALUE(PM_date_kpo[PM_Interval]), -- Convert to Integer using VALUE function
        6, DATEADD ( PM_date_kpo[PM1].[Date], 6, MONTH ),
        12, DATEADD ( PM_date_kpo[PM1].[Date], 1, YEAR ),
        PM_date_kpo[PM1].[Date]
    )
)

for PM3-PM10 the code is the exact same except the reference to the previous PM changes. The table below is a sample output from the query. I cannot understand why sometimes it works and other times it doesn't.

From my understanding, as long as Start Date isn't blank, then PM1 - PM10 should populate without issue however as you can see it does not, even when the previous PM has a value.

Is there any noticeable issue or way to mitigate this problem? 

SVMXC__Start_Date__c	PM1	PM3	PM4
10/1/2021	9/30/2022	9/30/2024
3/1/2022	8/31/2022	8/28/2023	2/28/2024
3/1/2022	8/31/2022	8/28/2023	2/28/2024
3/1/2022	8/31/2022	8/28/2023	2/28/2024
5/18/2022	5/17/2023

[123abc]

It looks like your code is attempting to calculate future service dates based on the SVMXC__Start_Date__c and PM_Interval columns. Since you are facing issues with inconsistent results, let's analyze the code to identify potential problems:

1. ISBLANK Condition: The ISBLANK condition in PM1 and PM2 is checking for blank values in different columns. Ensure that the columns you are checking for blank values are the ones you intend to use.

2. VALUE Function: You are using the VALUE function to convert PM_Interval to an integer. Make sure there are no unexpected characters or data types in the PM_Interval column that might cause issues with the conversion.

3. DATEADD Function: The DATEADD function is used to calculate future dates. Make sure that the base date, interval, and the resulting date are within valid ranges. Negative values for months in DATEADD might cause unexpected results.

4. Switch Statement: Double-check the logic within the SWITCH statement. Ensure that the cases cover all possible values of PM_Interval, and the calculations within each case are correct.

5. Data Quality: Check for inconsistencies or unexpected data in the columns involved. There might be cases where the data does not match the expected format, leading to unexpected results.

6. Testing: Consider running the code in a step-by-step manner or breaking it down into smaller parts to identify exactly where the issue is occurring. You can use intermediate columns to store values at different stages for debugging.

Here's a revised version of your code with some additional comments for clarity:

PM1 =
IF (
ISBLANK ( PM_date_kpo[SVMXC__Start_Date__c].[Date] ),
BLANK (),
SWITCH (
VALUE(PM_date_kpo[PM_Interval]), -- Convert to Integer using VALUE function
6, DATEADD ( PM_date_kpo[SVMXC__Start_Date__c].[Date], 6, MONTH ) - 1,
12, DATEADD ( PM_date_kpo[SVMXC__Start_Date__c].[Date], 1, YEAR ) - 1,
PM_date_kpo[SVMXC__Start_Date__c].[Date]
)
)

PM2 =
IF (
ISBLANK ( PM_date_kpo[PM1].[Date] ),
BLANK (),
SWITCH (
VALUE(PM_date_kpo[PM_Interval]), -- Convert to Integer using VALUE function
6, DATEADD ( PM_date_kpo[PM1].[Date], 6, MONTH ),
12, DATEADD ( PM_date_kpo[PM1].[Date], 1, YEAR ),
PM_date_kpo[PM1].[Date]
)
)

By carefully reviewing and debugging the code using the suggestions above, you should be able to identify and resolve the issues causing inconsistent results. If the problem persists, additional information about the data and the specific cases where the code fails could help in providing more targeted assistance.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.