15 Replies Latest reply: Nov 10, 2015 6:37 AM by Robert di rosa

# Filter value by time

Hello,

I have an application where there is data from couple years, but in order to make a screen more comprehensible i would like to make thee columns , one with data of the 356 last days , the other with the last 30 days , and the last with the last week.

Per example number of open tickets would be :

weekly  110

monthly 500

annualy 4'000

Now ui have only number of tickets since the beginning .... 11.48k

Do you have any clue how to achieve that  ?

best regards

• ###### Re: Filter value by time

can u attach the sample app qvf.?

• ###### Re: Filter value by time

Something like:

=Count({Date = {">=\$(=Date(Max(Date) - 7))"}>}, tickets)  // last week

=Count({Date = {">=\$(=Date(Max(Date) - 30))"}>}, tickets)   // last 30 days

=Count({Date = {">=\$(=MonthStart(Max(Date)))"}>}, tickets)   // since the start of the month

=Count({Date = {">=\$(=Date(Max(Date) - 365))"}>}, tickets)   // last 365 days

=Count({Date = {">=\$(=YearStart(Max(Date)))"}>}, tickets)   // since the start of the year

• ###### Re: Filter value by time

Hello Jonathan ,

i tried but i doesnt seem to work

=Count({Open_Date = {">=\$(=Date(Max(Open_Date) - 365))"}>}, RFC_Number)

and i get an empty result, do you have an idea ?

• ###### Re: Filter value by time

Make sure your Open_Date field contains date values and not string values, i.e. num(Open_Date) should return the numeric value of the date.

• ###### Re: Filter value by time

i have this line in my importation script

Date([Open_Date]) AS Open_Date,

is it enough to fix it  ?

• ###### Re: Filter value by time

Not if Open_Date contains text values. In that case you first need to use the Date# function. Date(Date#(Open_Date,'DD/MM/YYYY'), 'DD/MM/YYYY') as Open_Date. Adjust the date format strings DD/MM/YYYY as needed.

• ###### Re: Filter value by time

i've made many try , but it seems i cannot convert my date :-/, the field stay blank

here is my script line

date(Date#([SUBMIT_DATE_UT],'DD-MM-YYYY hh:mm:ss.[fff]'),'DD-MM-YYYY hh:mm:ss.[fff]')as Open_Date,

and a screenshot of the field in the database

• ###### Re: Filter value by time

Your date format doesn't seem to be DD-MM-YYYY hh:mm:ss.fff. It starts with years. But I suspect that it's a timestamp (aka datetime) field. Then Date(Open_Date) should give back date values too. Perhaps you can create a small qlik sense app with some example data so we can see what's going on.

• ###### Re: Filter value by time

Here it is

• ###### Re: Filter value by time

Ok, your source date field is a timestamp. You can create a date field from that like this:

"SUBMITTED_BY",

"SUBMIT_DATE_UT",

date(floor([SUBMIT_DATE_UT]),'DD-MM-YYYY') as Open_Date,

"DEPARTMENT_ID",

"RFC_NUMBER";

SQL SELECT "REQUEST_ID",

"SUBMITTED_BY",

"SUBMIT_DATE_UT",

"DEPARTMENT_ID",

"RFC_NUMBER"

FROM "EVO_DATA50004"."50004"."SD_REQUEST";

Once you have that the expressions posted above by Jonathan Dienst will work, provided you replace the field name Date with Open_Date and remove the comma from the expression. In the attached example I've used SUBMIT_DATE_UT and the timestamp function. Also, make sure to use case sensitive field names. To Qlik Sense RFC_Number and RFC_NUMBER are two different fields.

• ###### Re: Filter value by time

Hello,

so iv'e made the modification, and still doesn't work,iv'e also corrected the RFC_NUMBER fields. So i attached a new version of the App

But this syntax still a mystery to me , and i can't get the documentation to it :-/

Count({Open_Date = {">=(=Date(Max(Open_Date)- 365))"}}, RFC_NUMBER)

• ###### Re: Filter value by time

Search for set analysis: https://community.qlik.com/search.jspa?q=set+analysis

You'll find a number of blog posts and documents that will help.

Try this: Count({<Open_Date = {">=\$(=Date(Max(Open_Date)- 365))"}>} RFC_NUMBER)

• ###### Re: Filter value by time

there is changes, the result became 0 instead of "-"

From this expression

Count({<Open_Date = {">=\$(=Date(Max(Open_Date)- 365))"}>} RFC_NUMBER)

I tested :

(=Date(Max(Open_Date)- 365

Wich effectively return the date of one year ago

• ###### Re: Filter value by time

Then the date format of Open_Date probably is not the same as the document default. Try adding the date format in the expression:

Count({<Open_Date = {">=\$(=Date(Max(Open_Date)- 365,'DD-MM-YYYY'))"}>} RFC_NUMBER)

If the date format of Open_Date is not DD-MM-YYYY then change the expression and include the correct date format string instead.

• ###### Re: Filter value by time

It works like a charm yet  !  thank you very very much for both , your patience and your help