Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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

data to filter.png

Tags (1)
1 Solution

Accepted Solutions

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.


talk is cheap, supply exceeds demand
15 Replies
sureshqv
Esteemed Contributor III

Re: Filter value by time

can u attach the sample app qvf.?

MVP
MVP

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


(Adjust field names (Date and tickets) as per your requirement)

Not applicable

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.


talk is cheap, supply exceeds demand
Not applicable

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.


talk is cheap, supply exceeds demand
Not applicable

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 databasesubmit date.png

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.


talk is cheap, supply exceeds demand
Not applicable

Re: Filter value by time

Here it is