Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

view data in table based on its age

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?

1 Solution

Accepted Solutions
cjohnson
Partner - Creator II
Partner - Creator II

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

View solution in original post

10 Replies
pokassov
Specialist
Specialist

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.

Not applicable
Author

can you give example for what the script would be?

cjohnson
Partner - Creator II
Partner - Creator II

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

age.png

Please see attached for more detail.

Thanks,

Camile

Not applicable
Author

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)

WIP age.jpg

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
Partner - Creator III
Partner - Creator III

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

Regards,

Patric

pamaxeed
Partner - Creator III
Partner - Creator III

Not applicable
Author

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
Partner - Creator III
Partner - Creator III

what about the today function?

cjohnson
Partner - Creator II
Partner - Creator II

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