Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

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

12 Replies
beck_bakytbek
Master
Master

Hi Arina,

check this: https://community.qlik.com/thread/277269

i hope that helps

Beck

Anonymous
Not applicable
Author

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

OmarBenSalem

Try:

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

Anonymous
Not applicable
Author

didn't work

OmarBenSalem

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

Anonymous
Not applicable
Author

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.

Anonymous
Not applicable
Author

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.

Ricardo_Gerhard
Employee
Employee

Dear Ariana,

Have checked these functions?

Above - chart function ‒ Qlik Sense

Previous - script function ‒ Qlik Sense

Regards.

Ricardo Gerhard
OEM Solution Architect
LATAM
OmarBenSalem

In the script do as follow:

table:

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

LOAD

    No.,

    "Date" as DateField

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

(ooxml, embedded labels, table is Sheet1);

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

then in your sheet:

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())

Capture.PNG

and create a text chart with measure :

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

Result:

Capture.PNG