topic Select last 7 Days in QlikView Document in QlikView

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 12:29:59 GMT

Hi all,

is it possible to select only the data for the last week (or last 7 Days) in a QlikView Document, when opening it - automatically?
I saw the document triggers, but I dont know how to achieve this.

Maybe theres another way?
Would be nice, if someone could help me out with an example or something else.

Thanks!

Select last 7 Days in QlikView Document

gauravkhare — Mon, 20 Sep 2010 12:37:03 GMT

hi,

you can definately achieve this by the use of set analysis and it's also easy. All you need to do is a bit of r & d. If you face problem in set analysis kindly tell me and will be providing you with all the coding part.

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 12:40:34 GMT

I dont have any idea how to make this.

Would be nice if you could provide me an example or something else.
I have a table, which get the newest data from our database.
One column is the "Createn Date" (for example: 09/01/2010). I want, that only the data from the last 7 Days are selected when opening the document or when reloading it.

Thank you very much.

Select last 7 Days in QlikView Document

suniljain — Mon, 20 Sep 2010 13:00:01 GMT

Dear ,

I implemented same scenario with primavera.

ActiveDocument.Fields(

"WORK_YEAR"). Select ActiveDocument.Evaluate("Current_Year"

)

"WORK_WEEK"

). Select ActiveDocument.Evaluate("LastWeek"

)

"WORK_DAY"

). Select ActiveDocument.Evaluate("LastWeek"

)

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 13:16:26 GMT

Hi Sunil,

thanks for your answer and your suggestion. I tried your code in the module, but I dont know what I have to change, that it's working for my QlikView Document.

I attached a screenshot with a short description, so maybe you can tell me what I have to change in your code to get it to work.

Thank you very much.

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 14:11:44 GMT

I am currently experiencing a similar problem myself. I have created some flags in my load script which assigns a 1 against each date of last week but am struggling to find a flawless way of using it in a trigger or a macro. It seems like if you have a conflicting date selected, it won't work.

Yesterdays date will work: =only({1<DateYesterday = {'1'}>} Date (DateYesterday is another flag). The 1 in the set overrides a conflicting date already selected. But, you cannot use 'only' and have it select more than one date i.e. for each date of last week.

Perhaps if you have a week dimension you could do something like =only({1<DateLastWeek = {'1'}>} Week but if you only have a date dimension in your app it will not work.

I'll carry on trying to find a solution.

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 15:26:00 GMT

Do a selection for the field based on a search expression. For example, if your date field is named xxDate then make a listbox for the xxDate field, then type in =xxDate>now()-7 That should select the dates you are interested in. Then, make a bookmark (this will work best if xxDate is the only field selected). The bookmark will save the selection expression. Now you can set up a trigger and define the action as applying that bookmark. Make the trigger an On Open trigger so that it is applied when the document is opened. You can remove the listbox you created -- it is only needed as a place to enter the expression and check the result.

The nice thing about this solution is that it doesn't require any macro or module code.

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 15:55:25 GMT

Tim,

Nice idea but will this override any conflicting date selections already made?

Select last 7 Days in QlikView Document

Mon, 20 Sep 2010 16:14:11 GMT

Search expressions like that work within existing selections. So, if you already have a selection of dates from last February then the expression to select dates from the last seven days will not select anything (that is, it will not change your current selections). In a situation like that you must clear the selections on that field first. That is still something you can do with a triggered action.

As with ANY suggestions or solutions you find in this forum-- test them yourself in your own environment to make sure they satisfy your particular requirements!

Select last 7 Days in QlikView Document

Tue, 21 Sep 2010 07:34:35 GMT

Hi Tim,

thank you very much for your suggestion.
One more question. I created a listbox with my date field.
Where I have to enter now the expression you provided "=DATEFIELD>now()-7" to get the right selections?

Thanks!

Select last 7 Days in QlikView Document

Tue, 21 Sep 2010 09:06:38 GMT

You do a search and then bookmark it. So, select the object and start typing. This solution will give you a running 7 day dynamic selection.

Presumably, if you wanted to do a last full week selection, it's possible using this method. You would have to tweek the expression accordingly but I've not tried it.

Select last 7 Days in QlikView Document

Tue, 21 Sep 2010 21:05:29 GMT

When using a listbox, just click on the titlebar of the listbox to activate it, then start typing. Make sure the first character you type is an equal sign, = , so that QlikView knows you are typing an expression.

Select last 7 Days in QlikView Document

Anonymous — Fri, 29 Oct 2010 15:56:02 GMT

You can use this to select the current week.

=week(now())=week(Datum) and year(now())=year(Datum)

Re: Select last 7 Days in QlikView Document

Wed, 06 Jul 2011 16:31:08 GMT

First of all, thanks to Tim for his solution. This helped me figure something out that I was trying to do in the module for a while and is actually achievable without the bookmark solution.

On open you can set a 'Select in Field' action on the desired field, then use the following as your search string:

='=DATEFIELD>([Today] - [DaySelect])'

Where 'Today' is today's date and 'DaySelect' is the number of days you wish to subtract from the current date.

Again, thanks to Tim to providing the solution. This just possibly neatens things up a bit