topic Re: view data in table based on its age in QlikView

view data in table based on its age

Thu, 01 Oct 2015 12:43:30 GMT

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?

Re: view data in table based on its age

pokassov — Thu, 01 Oct 2015 13:01:03 GMT

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.

Re: view data in table based on its age

Thu, 01 Oct 2015 13:13:18 GMT

can you give example for what the script would be?

Re: view data in table based on its age

cjohnson — Thu, 01 Oct 2015 13:13:45 GMT

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

Re: view data in table based on its age

Thu, 01 Oct 2015 14:36:41 GMT

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??

Re: view data in table based on its age

pamaxeed — Thu, 01 Oct 2015 14:42:34 GMT

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

Regards,

Patric

Re: view data in table based on its age

pamaxeed — Thu, 01 Oct 2015 14:43:30 GMT

Re: view data in table based on its age

Thu, 01 Oct 2015 14:48:24 GMT

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 !!!

Re: view data in table based on its age

pamaxeed — Thu, 01 Oct 2015 14:50:37 GMT

what about the today function?

Re: view data in table based on its age

cjohnson — Thu, 01 Oct 2015 14:50:56 GMT

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

Re: view data in table based on its age

Thu, 01 Oct 2015 14:58:05 GMT

it strange that as I'd expect same behaviour on all records but is actually only a few very old ones that display with decimals values showing, and when I filter out completed work, to show only current jobs, which is all we need to see anyway, it all displays ok.

so happy with how it works at this stage

Thanks for the help