Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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)
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
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.
It works like a charm yet ! thank you very very much for both , your patience and your help