Number of days for selected data available (may be >366 days).

[QlikSenseUser_2021]

Hi,

In Qlik Sense, I need to calculate the number of days for selected data available.

I try :   Count(TOTAL DISTINCT DayNumberOfYear(Timing_Field_Data)) 

The result is good for one year. However if I select two or three years (filtering area) in order to know the number of days available for data available, the result is not correct.

Normally I can have a result more than 365 days but with this expression I get always a result less than 365.

Please, could you propose an adequate expression to get the desired result?

Thank you.

Best regards,

[Or · Accepted solution]

Why use DayNumberOfYear at all? Why not just count the total distinct dates? If the field is a date, just count it. If it has a timestamp aspect, you can floor(Date) or use DayName(Date)

[QlikSenseUser_2021]

Because I need to calculate the number of days and not how many datetimes I have in selected data.

According to your proposal, the result is the number of records that I have in data; this is not my target.

I give you a small example to clarify my idea, the data table contains:

ID                        datetime                        

1                         2021-10-01 08:10:00

2                         2021-10-01 09:10:00

3                         2021-10-01 10:10:00

4                         2021-10-01 11:10:00

The result should be : 1 day    and not    4 days

a second example is :

ID                        datetime                        

1                         2021-10-01 08:10:00

2                         2021-10-01 09:10:00

3                         2021-10-02 10:10:00

4                         2021-10-02 11:10:00

The result should be: 2 days   and not    4 days

Thank you.

[QlikSenseUser_2021]

I use this expression also to fix the problem:

Count(TOTAL DISTINCT date(Timing_Field_Data,'YYYYMMDD'))

it doesn't give the desired results.

[QlikSenseUser_2021 · Accepted solution]

Thank you for your response, I change the expression and it's work :

Count(TOTAL DISTINCT floor(date(Timing_Field_Data)))