How to make a date filter when some of those fields are null?

Question & Answer
, , ,
1

Hi guys,

I have a table with three date fields. Those date are not equals.
Some of those rows, those fields are null.
I must have a filter by date, but it is not works when the field is null.
How to make a filter that it works for those dates, even that some of those rows are null?

I need to do it because I need to make a calculated field “Day over Day” and “Month over Month”

Ex: periodOverPeriodPercentDifference(distinct_count({id_customer}), {date_pp}, DAY, 1)

Filter: date_pp

The ‘date_pp’ field is null in of most the rows.

Tks!

2

Hi @July
whats the difference between the date columns?
Could you merge the columns into a extra column? like

ifelse(isNotNull({date1}),{date1},isNotNull({date2}),{date2},isNotNull({date3}),{date3},NULL)))

BR

1 Like
3

In fact, there are three related tables, and each date has its value, they are not the same between them.
When I filter by the date of the third table that is most important to me, the two join tables that have this null field do not appear

4

How is the definition of the join? (left/right/inner/full)?

Your tables look like that?

grafik
grafik910×101 2.35 KB

1 Like
5

Yes, exactly!

By left join

6

So you will only get the left values

grafik
grafik598×513 74.9 KB

What if changing to full?

1 Like
7

I think if I change to full join, I may have some problems.

By the way, Do I need to put the date column in the join? I have other columns in the join…

8

you said the dates have different meanings, so i would say no.

9

Is there a way to make a calculated field that, if it doesn’t have the filter date, brings a ‘secondary’ date?

10

as mentioned before
new_date =

ifelse(isNotNull({date1}),{date1},isNotNull({date2}),{date2},isNotNull({date3}),{date3},NULL)))
1 Like
11

The calculated field does not work.
The sintaxe is wrong :frowning:

12

pls share your definition

13

ifelse(isNotNull({dat_c}),{dat_c},isNotNull({dat_s}),{dat_s},isNotNull({dat_sg}),{dat_sg},NULL)))

dat_c : table1
dat_s: table 2
dat_sg: table 3 … relevant date

Ex:
Cup | dat_c| sim | dat_s | pro | dat_seg | cli
123 | 10/10/2023 | null | null | null | null | 999
221 | 05/01/2023 | 444 | 23/01/2023 | null | null | 111
908 | 10/10/2023 | 2 | 11/10/2023 | 2220 | 12/11/2023 | 56

14

ifelse(isNotNull({dat_c}),{dat_c},isNotNull({dat_s}),{dat_s},isNotNull({dat_sg}),{dat_sg},NULL)

2 Likes
15

Erick,

Cool, it looks like working, but does not work when I add the Day Over Day field.

new_date = ifelse(isNotNull({dat_c}),{dat_c},isNotNull({dat_s}),{dat_s},isNotNull({dat_sg}),{dat_sg},NULL)

periodOverPeriodPercentDifference(distinct_count({id_customer}),{new_date},DAY,1)

16

Hi @July,
Any error message?
BR

17

I don’t know why, but something was updated and started working

Thank sou much!

18

This command says:
Bring the first non-null date, correct?

1 Like