Date Line Item using Min Summary

Hello

I am using line item formatted as date with 6 level hierarchy. I am using summary as Min for the same.

I am facing issues where if there is no date in my list P6, i am getting 12/31/2399 for all NonBlank list members. Please advise best way to handle it without doing any hardcoding.

Regards

MO

Answers

  • Hello, Thanks for looking into it, but it doesn't work incase there are no P6 list members. Also, I do not want to hardcode P6 list member & it should apply to wherever P6 members are available. Please advise.

  • Example screenshot below. If there is no L6 member, date automatically comes up as 12/31/2399. I am using a MIN Summary function here.

    But incase I have atleast 1 L6 member, it looks good. Please advise.

  • This formula determines whether the date in P6 doodle jump is blank. If it is, it will return an empty string ("") or any other value you request. If it is not blank, it uses the Min summary function to calculate the minimal value.

  • mohitohri1811
    edited May 2023

    What should be the result incase there is no P6 list member ?

    In current scenario, if my p6 member is blank or non blank, all p5 till p1 looks good. However, incase there is no P6 member, all other lists from P5 till P1 shows 12/31/2399 due to Min Summary I am using. Please advise.

  • It will just return on an empty string. Thank you for confirming this. P1 to P1 will stay looks good. mobile massage

  • It will just return on an empty string. Thank you for confirming this. P1 to P1 will stay looks good. mobile massage

  • scottish21
    edited August 2023

    Anyway, you should put p6 there. That's the lacking of your formula. It will not work unless you have the p6. landscaping

  • Hi MO,

    I see you're facing a date formatting issue with P6. To handle this without hardcoding, consider using conditional formatting or formulas to check for empty values. More details about your setup would help provide specific advice.

    Cantordanielpincus.com

  • Hello MO,

    To address the issue without hardcoding, consider using conditional formatting to hide or format cells displaying 12/31/2399 for non-blank list members. Alternatively, create a system module with a more suitable summary method like "Last" for better date handling.

    Best regards,
    Tree Pruning

  • Hello MO,

    To resolve the issue of receiving 12/31/2399 for NonBlank list members in the absence of a date in P6, consider using a conditional formula to check for the presence of a date. You can utilize an IF statement to set the default output to something more suitable, like "N/A" or "No Date" when no date is present. This approach avoids hardcoding and ensures flexibility in handling the issue.

    Best regards,

    Grand Rapids Basement Pros

  • Hello MO,

    To address the issue of receiving 12/31/2399 for NonBlank list members in list P6, without hardcoding, create a system module with a line item "Last Valid Date" to dynamically find the latest valid date from P6 using a formula. Then, in your original module, reference this line item for your summary calculation instead of using Min. This approach ensures that your summary date adapts dynamically to the last valid date within the P6 list. check it here.

  • Consider adjusting the summary function to something other than Min, such as FirstNonBlank or LastNonBlank, to handle the issue of 12/31/2399 without hardcoding. This may provide more meaningful results for non-blank list members. See more


  • I understand your point; thank you for sharing. I'm delighted to have discovered this website via Google.

    Custom Boat Lifts

  • To avoid the 12/31/2399 issue, try using this summary formula:

    scssCopy codeSummary = IF ISBLANK(LineItem.Date) THEN BLANK() ELSE MIN(LineItem.Date)
    
    

    Let me know if you need more help.

  • @watermelon game Your formula is incomplete and will not be effective without the p6.