Date comparison for component chart and tables

I would like to compare the chart and tables based on different dates.
The date picker would be a good pick because it ensures that a valid date is inserted. Currently, the date picker is affecting the whole sheet.
Is there a possibility to get something similar to a date picker that affects only one component on the sheet?

Thanks a lot for the help

Paul

Hi Paul,

You can use a ‘Button dropdown’ component to achieve this effect. This is how to it:

  1. Create two variables (click on the three lines Icon analysis%20options%20icon and then select ‘variable’) call them “start_date” and “end_date”

  2. Create a ‘Button dropdown’ component. Give it the following name: “Start date: <%= start_date%>”

  3. From the dropdown in the component options select “Load Entries”.

  4. Click on the formula icon formular%20icon next to the black box to open the formula editor

  5. In the formula editor write the following formula:

CASE

WHEN DAY("_CEL_P2P_ACTIVITIES".“EVENTTIME”) < 10 AND MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) < 10

THEN YEAR("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || ‘0’ || MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || ‘0’ || DAY("_CEL_P2P_ACTIVITIES".“EVENTTIME”)

WHEN MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) < 10

THEN YEAR("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || ‘0’ || MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || DAY("_CEL_P2P_ACTIVITIES".“EVENTTIME”)

ELSE YEAR("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || MONTH("_CEL_P2P_ACTIVITIES".“EVENTTIME”) || ‘/’ || DAY("_CEL_P2P_ACTIVITIES".“EVENTTIME”)

END

(Explanation: the dates need to have the structure ‘dd’/‘mm’/‘yyyy’ so that it can be converted into a time stamp in the filter later. But MONTH(“TABLE”.“COLUMN”) returns a single digit number of all months before October so an extra ‘0’ has to be stuck on the month part of the date has two digits for timestamps from the effected months. On the same principle to timestamps that take place before the 10th of the month also need to get an extra ‘0’ added.

  1. Back in the component options select the variable “start_date” in the drop down next to “write to variable”. Also untick the box next to “Allow multiple selections”.

  2. Make a copy this component (right click on the click on the component, select copy component, then right click on some empty space on the analysis sheet and click paste)

  3. In this copy, change the title to "End date: <%= end_date%> " and change the variable in the drop down next to “write to variable” to “end_date”

9 Now the following component filter can be added:

FILTER DAYS_BETWEEN(TO_TIMESTAMP(’<%= start_date %>’, ‘YYYY/MM/DD’), “_CEL_P2P_ACTIVITIES”.“EVENTTIME” ) >= 0;

FILTER DAYS_BETWEEN("_CEL_P2P_ACTIVITIES".“EVENTTIME”, TO_TIMESTAMP(’<%= end_date %>’, ‘YYYY/MM/DD’)) >= 0;

(Explanation: TO_TIMESTAMP() converts the variables, which have the type text back to dates. DAYS_BETWEEN() calculates the days between the first date and the second date. If the second date is earlier, than the first it returns a negative number. As only dates are aloud that are after the start date and before the end date the differences must be greater than 0.)

Does this solve your problem? Is there anything else we can help you with?

Best wishes,

Calandra

2 Likes

Hello Calandra,

This would solve the problem, however it gives the user the Freedom to type in text field. We would rather force the user to pick a date, this also so that the user always picks the right date format, and there is no confusion about this. So your solution works, when we want to compare two components on different dates, however it is not the optimal solution.

Best,
Paul Velthuis

Hello Paul,

I don’t understand what the problem is. “Button dropdown” components don’t allow users to write anything. They can only pick from a predefined list of options. Only “Variable Input” components allow users to write anything. Could you please send me a screen shot of your implementation to help me identify the problem?

Best wishes,

Calandra

Hello calandra,

If I am correct you are suggesting to create two “Button Dropdown” buttons.

  1. Comparison date 1
  2. Comparison date 2

For time ranges this would mean that I would need a total of 4 “Button Dropdown”.

  1. Comparison start date 1
  2. Comparison end date 1
  3. Comparison start date 2
  4. Comparison end date 2

Thanks for the explanation this would completely work and indeed force users to enter a date.

Best regards,
Paul Velthuis