Filter value by time

[#1]

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

[Gysbert_Wassenaar]

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

LOAD "REQUEST_ID",

    "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.

[#3]

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)

Thank you, for your help

[Gysbert_Wassenaar]

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)

[#5]

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

[Gysbert_Wassenaar · Accepted solution]

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.

[#7]

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