topic Automatically selecting a week in QlikView

Automatically selecting a week

Sun, 25 Sep 2011 20:03:39 GMT

Hello,

On weekly basis I am loading new data to my database. In QV I have a simple list box that allows me to choose a reporting week. Say, for example, I have 10 weeks worth of data in my QV app. On Monday I will load week 11 data. How can I make QV automatically select the latest week (week 11) in the week selection list box to report on it?

Than you.

Automatically selecting a week

swuehl — Sun, 25 Sep 2011 21:16:18 GMT

You could do this using so called triggers and actions in QlikView.

First select a trigger, if you want something be done automatically you could go to document properties, tab triggers and select an "OnOpen" trigger. Or go to sheet properties, tab triggers and select "OnActivateSheet".

Then you need to add an action, action type is "Selection", action "Select in Field".

Set the field to your field name for week, I assume

week

(as is, no equal sign, just the name in the input field).

Then enter as search string

=max(week)

This should do the trick.

Regards,

Stefan

Automatically selecting a week

Sun, 25 Sep 2011 22:08:55 GMT

Hi Stefan,

Thanks for the answer. In principle this works. However, I missed to provide a crucial piece of info

week and year numbers (i also want to filter on year) reside in 2 tables:

1) calendar master

2) sales

calendar master carries week numbers (52) for entires years (2010-2015). My sales table holds data for only uploaded weeks. The problem with the =max statement is that i always get week 52 and year 2015 selected.

Is there a workaround to only select max year 2012) and max week (11) from the sales table?

Thanks,

Automatically selecting a week

swuehl — Sun, 25 Sep 2011 22:21:59 GMT

Use maybe something like this as search string:

=max({<week = {"=sum(sales)>0"}>} week)

So search for weeks with sales > 0 (maybe you need to put an appropriate measure in here), and get the max out of this set. Use similar for year.

You probably get into troubles with separating year and month and looking for max values independently. Do you have a continouus dimension like an OrderDate or WeekID?

Regards,

Stefan

Automatically selecting a week

Sun, 25 Sep 2011 23:30:07 GMT

Thanks for this.

Max year works - 2012 is selected

Max week is still selecting wk52.

I do have end-of-week date as a continuous dimension.

Regards

Automatically selecting a week

swuehl — Mon, 26 Sep 2011 20:04:44 GMT

Yes, that's what I wanted to say with getting into troubles.

I think what might work is this:

use a Select in Field action for field end-of-week with search string

=max({<endofweek = {"sum(sales)>0"}>} endofweek)

(maybe =max(endofweek) is enough if this end of week date is limited to your weekly data only)

This should select the max end of week date, and results in a possible selection for one year and one week (one white value in each list box indication possible value).

If you want an explicite selection of year and month (green values in list box), you could add two more actions, with action type "Select Possible" and Field year resp. week.

Hope this helps,

Stefan