• Replies 1 reply
  • Subscribers 962 subscribers
  • Views 33 views
  • Users 0 members are here
Related

"Plan Days in Current Status" negative numbers?

Kelly Cronin
$organization

Hi! 

I am trying to set up a dashboard in the Plans Cube where the widgets are looking at the MAX(Plan Days in Current Status) value for a given plan status. However, I'm seeing some negative numbers for this value, and a negative value doesn't make logical sense in this context (if a positive number is how many days in the past the status was changed, is a negative number implying that this plan status going to be scheduled to change in the future?).  As I've done some spot checking, the best I can figure is that if I uploaded a plan in the status I'm pulling on, like Cultivation, then that plan will show a negative value for Plan Days in Current Status rather than the duration between the upload day and today/the most recent datacube update. This occurs when I use other value types (sum, count, min, etc), as well.

Has anyone else experienced this behavior? Or maybe some suggestions to work around it?  I don't want to filter out all negative Plan Days in Current Status since I still want to track those plans, but I'm afraid that the negative values will cause confusion for the end-users.

Thank you!

Parents
  • $organization

    Hi Kelly,

    When the plan has no prior plan status, the Plan Days in Current Status is currently being calculated as the time between the Plan Start Date and Today, as opposed to that upload date. When the Plan Start Date is in the future, then the Plan Days in Current Status as currently designed, will be negative. Does that align with what you're seeing? 

    You could work around this by creating a formula that groups on Plan ID and IF the Days in Current Status in < 0, the report it as 0. This will work so long as Plan ID is not a visible field on your widget.

    MAX ( [Plan ID] , IF ( MAX ( [Plan Days in Current Status] ) < 0 , 0 , MAX ( [Plan Days in Current Status] ) ) )

    Or, if you want to return the time since the plan was created...

    MAX ( [Plan ID] , 
       IF ( MAX ( [Plan Days in Current Status] ) < 0 , 
          MAX ( DDIFF ( [Days in Create Date] , NOW ( [Days in Create Date] ) ) ) , 
          MAX ( [Plan Days in Current Status] ) 
       ) 
    )

Reply
  • $organization

    Hi Kelly,

    When the plan has no prior plan status, the Plan Days in Current Status is currently being calculated as the time between the Plan Start Date and Today, as opposed to that upload date. When the Plan Start Date is in the future, then the Plan Days in Current Status as currently designed, will be negative. Does that align with what you're seeing? 

    You could work around this by creating a formula that groups on Plan ID and IF the Days in Current Status in < 0, the report it as 0. This will work so long as Plan ID is not a visible field on your widget.

    MAX ( [Plan ID] , IF ( MAX ( [Plan Days in Current Status] ) < 0 , 0 , MAX ( [Plan Days in Current Status] ) ) )

    Or, if you want to return the time since the plan was created...

    MAX ( [Plan ID] , 
       IF ( MAX ( [Plan Days in Current Status] ) < 0 , 
          MAX ( DDIFF ( [Days in Create Date] , NOW ( [Days in Create Date] ) ) ) , 
          MAX ( [Plan Days in Current Status] ) 
       ) 
    )

Children
No Data