How set values without expiration date?

DaGi07
Participant V

Hello to everyone,
I have a little problem with the data type. In particular, I have a list of certificate of training course but some course have a expiration date and other no. The problem is for the certificate without expiration date because in app view appears “invalid date” where in DB is set the values “NO”. How can I show in the app that for some certificate there is no expiration date instead of “invalid date”
3X_d_d_dd3d40b26244260efa188d3a3b8e4dde35411dea.png

TKS in advance!

Solved Solved
0 7 296
1 ACCEPTED SOLUTION

@DaGi07 Could you achieve your goal using a virtual column instead of the real one?

Database
To start with, I would definitely not mess with the database (see @Steve ). “No” is a no-no as a date. That said, a blank value would be perfectly fine to convey the fact that the expiration date is not set.

Slice
If the need is to filter out the invalid dates, then a slide would indeed do the job. But again, just leave these dates blank.

Display
Otherwise, if you actually want to show all dates, including the blank one, but display No instead of [blank] like it is in your chart, you could try using a virtual column for instance.

Let’s call it [VirtualDate], and make it of type Text (not Date). It would display the actual date if set, or ‘NO’ if blank.

The formula could look like:

IF ( ISBLANK( [Date] ), “No”, [Date] )

Not sure it would sort properly in the chart, but it definitely would show “No” instead of “[blank]”. Just an idea, trying to work around your issue.

View solution in original post

7 REPLIES 7

Steve
Participant V

Are you actually using a database? Or are you using a spreadsheet?

Either way, a column of a temporal type (date, time, or date/time) should not ever have NO as a value.

DaGi07
Participant V

Hi Steve,
I’m using a spreadsheet in which the columns “EXPIRATION DATE” contains or the date or the value NO. How can i have a better visual in the app for the training courses that don’t have an expiration date?

Why not just leave the cell blank if there is no expiration date?

DaGi07
Participant V

Because my idea is also to track in a chart view the expiration date in order to monitoring the date and how many certificate are expiring.
How you can see, the first histogram is blank… i would not that the values blank appears in my chart

@DaGi07 Could you achieve your goal using a virtual column instead of the real one?

Database
To start with, I would definitely not mess with the database (see @Steve ). “No” is a no-no as a date. That said, a blank value would be perfectly fine to convey the fact that the expiration date is not set.

Slice
If the need is to filter out the invalid dates, then a slide would indeed do the job. But again, just leave these dates blank.

Display
Otherwise, if you actually want to show all dates, including the blank one, but display No instead of [blank] like it is in your chart, you could try using a virtual column for instance.

Let’s call it [VirtualDate], and make it of type Text (not Date). It would display the actual date if set, or ‘NO’ if blank.

The formula could look like:

IF ( ISBLANK( [Date] ), “No”, [Date] )

Not sure it would sort properly in the chart, but it definitely would show “No” instead of “[blank]”. Just an idea, trying to work around your issue.

Firstly, I would like to thanks you for your the great attention that you give to my question.
Unfortunately, the values in the graph are not sorted as you told me. Maybe, I tried to filter the invalide date and remove these date from the graph.
The result is this that you can see in the screen.

Top Labels in this Space