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
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.
can u attach the sample app qvf.?
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
(Adjust field names (Date and tickets) as per your requirement)
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 ?
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.
i have this line in my importation script
Date([Open_Date]) AS Open_Date,
is it enough to fix it ?
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.
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
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.
Here it is