default time frame - VB help

[#1]

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

[#2]

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.

[Oleg_Troyansky]

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.

[#4]

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

[#5]

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

[#6]

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.

[Oleg_Troyansky]

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

[rwunderlich]

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