topic Re: Macro to select latest 25 months in QlikView

Macro to select latest 25 months

Fri, 13 Nov 2015 07:57:25 GMT

Hello everyone,

I want to replace one of the on open triggers with macro.

Can anyone tell me how to write a macro to select latest 25 months.

Below does not work

Activedocument.Evaluate("=date(AddMonths(date#(max(DATA_FIELD),'YYYYMM'),-24),'YYYYMM')")

Thanks

Khushboo

Re: Macro to select latest 25 months

marcus_sommer — Fri, 13 Nov 2015 09:30:33 GMT

I think this isn't a good idea then everything what you could to do within the gui including actions shouldn't be done per macro, see: Macros are Bad.

If the search-expression within your OnOpen action worked it's ok. if not you could consider to simplify such search-expression with an additionally field MonthCounter, here an example:

Re: Howto make a variable to calculate quarters regarding year changes?

- Marcus

Re: Macro to select latest 25 months

Fri, 13 Nov 2015 09:49:23 GMT

Thanks Marcus for your answer.

I know macros are bad. but i need this post reload macro for default selections which works fine.

Performance for on open triggers is bad. Hence i wanna write few on open triggers in post relaod macro.

Can anyone help me on this?

Re: Macro to select latest 25 months

jonathandienst — Fri, 13 Nov 2015 10:03:45 GMT

I agree with Marcus. If you move to server reloading and hosting on a portal, neither OnPostReload or OnLoad will work, as the event triggers do not fire. Rather design your app using logic like this example:

Define a variable, for example in your load script):

Set vMax25 = '=Date(AddMonths(max(DATA_FIELD),-24))';

Now use the variable in your set expressions like:

Sum({<Date = {">=$(vMax25)"}>} Amount)

And in the expression or chart label, you could use an expression like:

='Amount since ' & vMax25

The '=' sign in the variable definition means that the expression is calculated globally, and is updated as soon as the user makes a selection as the max(DATA_FIELD) will change as well.

Re: Macro to select latest 25 months

petter — Fri, 13 Nov 2015 10:19:32 GMT

In your specific case it is very helpful to make a calculated field called MonthsAgo which you can calculate from the DATA_FIELD like this:

LOAD

......

Year(Today())*12 + Month(Today()) - Year(DATA_FIELD)*12 - Month(DATA_FIELD) AS MonthsAgo

......

Then you can make a static bookmark or a search that is like this:

MonthsAgo<=24

I think you should be able to do without a macro or trigger then... maybe...

Re: Macro to select latest 25 months

marcus_sommer — Fri, 13 Nov 2015 10:24:10 GMT

Your macro would need to look like:

var = Activedocument.Evaluate("date(AddMonths(date#(max(DATA_FIELD),'YYYYMM'),-24),'YYYYMM')")

ActiveDocument.Fields("DATA_FIELD").Select ">=" & var

But if you have performance issues you should consider the suggestion from Jonathan and some more things, like a fewer open objects, using from calculation-conditions within the objects, using of a landing page ....

- Marcus

Re: Macro to select latest 25 months

rubenmarin — Fri, 13 Nov 2015 10:25:31 GMT

Hi khushboo, I would listen to Marcus and Jonathan and avoid macros if possible, anyway if you want the macro you can try:

vVar = Activedocument.Evaluate("date(AddMonths(date#(max(DATA_FIELD),'YYYYMM'),-24),'YYYYMM')")

ActiveDocument.Fields("DATA_FIELD").Select "<" + vVar