view data in table based on its age

[#1]

Hello

I'm trying to add method into my Qlikview app of filtering data held within a table based on how long its been held open.  We have a field called "Date Requested" and want users to be able to see all records logged for defined time period:

0-4 Weeks

4-8 Weeks

8-12 Weeks etc.

I've used variables to simply count the number of jobs in these dates ranges which are,

vWIP<4Weeks:

count(DISTINCT{<status_cat={open},probtype_cat={reactive}>} if(vToday-[Date Requested]<=28, [Work Request Code]))

vWIP4-8Weeks:

count(DISTINCT{<status_cat={open},probtype_cat={reactive}>} if(vToday-[Date Requested]>28 and vToday-[Date Requested]<=56, [Work Request Code]))

And these work fine to give give a total number of jobs which fall within these timeframes.  But I'm finding it a little harder to use the same variables to filter the actual records in the table.  Can I use these same variables for this of do I need something else?

What I'm trying to set up is a Button, with an action.  So far I've tried the "Select in Field" action with [Date Requested] as the field and then trying to use the variables in the search string, but to no success so far.

Can anyone help?

[pokassov]

Hi!

Did you see the approach then you add field "defined time period" into your model?

You can calculate it during relod and then use it.

[#3]

can you give example for what the script would be?

[cjohnson]

Hi Dan,

Try adding a field in your script that actually gives you the age. Something along the lines of

LOAD

today() - [Date Requested] = Age

FROM

Please see attached for more detail.

Thanks,

Camile

[#5]

OK, thanks so far, nearly there!!

But I'm getting this result from the code I've used in the script (based on your example)

the script is:

Load
*,
if(today(1) - [Date Requested] <=28, '0-4Weeks',
if(today(1) - [Date Requested] >28 and today(1) - [Date Requested]<=56, '4-8Weeks',
if(today(1) - [Date Requested] >56 and today(1) - [Date Requested]<=84, '8-12Weeks',
if(today(1) - [Date Requested] >84 , '>12Weeks',       ))))
as [WIP Age Group] ;

load
*,
today(1) - [Date Requested] as Age;

the "Age" for the first item in the list (requested on 29th May 2012) should be 1220!!  Some are rounding up some are showing decimals - whys this??

[pamaxeed]

Because probably you have a timestamp in your data, you can use floor() or ceil() function to avoid this behaviour....

Regards,

Patric

[pamaxeed]

[#8]

the timestamps have been removed in the ETL script (with all date fields have DD/MM/YYYY format) to create the QVD files which then provide the data for this model, so there shouldn't be any time stamps, other wise I'd agree with you !!!

[pamaxeed]

what about the today function?

[cjohnson · Accepted solution]

Even though the time stamp may have been removed - it is likely that Qlikview is still keeping the internal time stamp value (depending on what you are doing in the ETL process to remove the timestamp).

You can also try using the function "DayStart" to convert your [Date Requested] to the start of the day (meaning it would convert the timestamp to end with 00:00:00).

So your load would instead look something like this:

if(today(1) - DayStart([Date Requested]) <=28, '0-4Weeks',
if(today(1) - DayStart([Date Requested]) >28 and today(1) - DayStart([Date Requested])<=56, '4-8Weeks',

etc....

Camile