Select dates in dropdown

Dear Colleagues,

I have the following problem: I am calculating the DAYS_BETWEEN certain events and if they are more than 1 day, it is to be counted in KPI.


There are exceptions to this standard rule for month end closing period. I would like to list those dates (or allow users to select them accordingly to closing calendar) and if the date of order is for these particular cut off dates, then is it also acceptable.
I created a dropdown and even tried to convert it to date, but I think it is kept as string by definition. Is there any other way to include these exception dates?
image

Hi,

thanks for reaching out to us! In fact, storing values of type date into a variable and then comparing another date field with that list is a bit tricky. I replicated your issue and the following solution worked for me:

Within the button dropdown component, you can split the date into its year, month and date and store this value as a string into the variable:

YEAR(“ACTIVITIES”.“EVENTTIME”)
||’-’||
MONTH(“ACTIVITIES”.“EVENTTIME”)
||’-’||
DAY(“ACTIVITIES”.“EVENTTIME”)

Now, you simply need to use the same logic within your KPI. After the AND you can insert:

… AND
YEAR(“ACTIVITIES”.“EVENTTIME”)
||’-’||
MONTH(“ACTIVITIES”.“EVENTTIME”)
||’-’||
DAY(“ACTIVITIES”.“EVENTTIME”)
NOT IN (<%= Cutoff %>)

You just need to exchange the column “ACTIVITIES”.“EVENTTIME” with your respective date column. This should lead to your desired result. The only downside of this solution is the date’s representation within the dropdown component as leading zeros are not shown (e.g. February = ‘2’ and not ‘02’). However, if this is a problem for you, you can simply use a CASE WHEN statement within the above-proposed solution to add these leading zeros. If this solution does not work for you or you have another question, please feel free to reach out!