topic default time frame - VB help in QlikView

default time frame - VB help

Tue, 11 Jan 2011 21:15:40 GMT

Hello,

my requirement is to show the last 3 months of data by default (on opening the document and on some sheets).

I think the way to do this is by writting a macro to pull data where FiscalMonth >= today() - 4 ....roughly, and set that up as an Action in the Sheet Properties.

Could someone help me with the VB syntax for this? Unless there is a better way to do this without writting a macro.

The FiscalMonth field is in the following format: 200901, 200901, 200903...

Thanks, Maria

default time frame - VB help

Tue, 11 Jan 2011 21:27:50 GMT

Set Analysis would probably be preferred. Something like:

Sum({<FiscalMonth>={($=Today()-4)}>} Sales)

You'll probably need to adjust that a bit depending on your data formats. Regardless of the formats, Set Analysis should be able to solve the problem.

The second best way is with Actions. Actions allow macro functionality without requiring you to write VB code. This can be done using a Select Action in the OnOpen event of the document. Search string would be similar to the expression you posted.

The final method would be the macro. You would use ActiveDocument.Fields('FieldName').Select 'SEARCH STRING' and use the same search string from the Action.

default time frame - VB help

Oleg_Troyansky — Tue, 11 Jan 2011 21:33:12 GMT

Maria,

I'd rather take you back to your earlier idea of selecting dates using special choices like "Last 3 Months", "Last 6 Months", etc... If you create such a field in your calendar, your job will be as simple as adding an Action on each required sheet and selecting a field value "Last 3 Months". QlikView will do the rest, and the user will be able to modify the selection when needed.

default time frame - VB help

Wed, 12 Jan 2011 21:36:51 GMT

NMiller,

I think option 2 would be best. Unfortunately, I am having a hard time with that syntax as well.

This is what I am doing in the Search String but it doesn't work. [Fiscal Month] is a field loaded in the script with no date formatting, and has the following values: 201010, 201011, 201012....Any help?

date([Fiscal Month],'YYYYMM') > AddMonths(date(Today(),'YYYYMM') ,-4)

Thanks!

Maria

default time frame - VB help

Wed, 12 Jan 2011 23:08:41 GMT

Oleg,

yes, that works. I am doing the following in the script, and then I set up an Action at the Document level.

if (date#(FISCAL_MONTH,'YYYYMM') > AddMonths(date(Today(),'YYYYMM') ,-4),'Last 3 Months') as [Time Grouping]

I am just curious how this would work with NMiller's suggestion.....I tried typing " > AddMonths(date(Today(),'YYYYMM') ,-4)" in the String Seach box, but it doesn't seem to work.

Thanks, Maria

default time frame - VB help

Thu, 13 Jan 2011 16:57:44 GMT

You have to be careful with the Search String it appears to be very picky. In your string you had: double-quote space greater-than space (" > Add...). I had to remove those spaces to get it to work. I also made some modifications that got this to work on a date field in my data set. The expression is returning a date, so if your date fields are really just strings, you may need to modify it further to get it working.

Here's what I used:

='>$(=AddMonths(date(Today(),YYYYMM) ,-4))'

The equals sign is needed to start. I usually stick with single-quotes (don't know if that is required). You also don't need the single quotes around the date format since it is embedded in another set of quotes. Finally, I used a dollar sign expansion to force the AddMonths function to evaluate before performing the search. I don't know if all of those changes are required, but it worked on a date field in one of my applications.

default time frame - VB help

Oleg_Troyansky — Fri, 14 Jan 2011 21:43:17 GMT

Maria,

if it still does not work after the last correction from NMiller, try formatting the date after applying AddMonths, and not before. The function AddMonths() returns a date field, and it might have to be formatted as well...

default time frame - VB help

rwunderlich — Fri, 14 Jan 2011 23:42:12 GMT

Maria,

Another approach is to make all your initial selections using search expressions. Then save as a bookmark and apply the bookmark as your Action. See attached for an example.

-Rob