12 Replies Latest reply: Jan 11, 2018 6:50 AM by omar bensalem

# Number of days between dates

I am working on an app, and there are customer complaints. If their is a complaint the date exists, if not the date is not in the application.

We want to calculate the record number of days between complaints and the current number of days without a complaint.

Could you help me figure out the formulas? looking to do KPIs.

Thank you

Ariana

• ###### Re: Number of days between dates

Hi Arina,

i hope that helps

Beck

• ###### Re: Number of days between dates

I don't think that would work as they have two columns a to date and from date. I only have one date column.

• ###### Re: Number of days between dates

Try:

date(date#(YourDateField,'DD/MMM/YYYY'))-today(1)

• ###### Re: Number of days between dates

didn't work

• ###### Re: Number of days between dates

put ur date field into a filter; and show us an image?

and then create a new filter : Month(YourDate) and show us the image (I want to see if qlik's recognizing ur date as a date or not)

but, before taht, try date(YourDate)-today(1)

or YourDate-today(1)

Obviously, put that in a table, with a dimension: No. to aggregate your calculaiton

• ###### Re: Number of days between dates

Qlik is recognizing my date as a date. I already have Month, Year , Date and Day filters in this application, all show as what they should be.

I tried date("Date")-today(1) and "date" - today(1) and neither worked.

• ###### Re: Number of days between dates

These dates are dates of complaints filed with us from our customers. We want to know the maximum number of days we have had this year between complaints. And currently how many days we are without a complaint currently.

• ###### Re: Number of days between dates

Dear Ariana,

Have checked these functions?

Above - chart function ‒ Qlik Sense

Previous - script function ‒ Qlik Sense

Regards.

• ###### Re: Number of days between dates

In the script do as follow:

table:

load * , DateField- Previous(DateField) as NumberOfDays ;

No.,

"Date" as DateField

FROM [lib://DOSSIER telechargements/Data Qlik Question.xlsx]

(ooxml, embedded labels, table is Sheet1);

let vMaxDate= peek('DateField',-1,'table');

1) Create a table:

dimension:

= 'difference between' & aggr([No.]&'-'&Above([No.]),[No.])

u can change No. by ur dateField

Measure:

Sum(NumberOfDays)

Color ur measure by expression:

if(Sum(NumberOfDays)=max(all NumberOfDays),Green())

and create a text chart with measure :

=' Number of days without complaints :'& (Today(1)- vMaxDate)

Result:

• ###### Re: Number of days between dates

have this answered ur question? arianaford

• ###### Re: Number of days between dates

It did not help me to make the KPI that has been requested. I feel as though it doesn't give a clear answer in a table form.

Thanks though for the help

• ###### Re: Number of days between dates

first of all, you could have thanked me for my time..........

second of all, I already provided with an expression u could use in your KPI:

max(all NumberOfDays) (Which I used in the coloring of the table...)

and an expression for number of days without complaints:

Today(1)- vMaxDate

you're welcome...........