Is it possible to run a macro for each row of a chart (table)

[#1]

Hi all,

I'm new to Qlikview and have had a search around but couldn't find what I'm after.

I have a chart which has a 1 dimension (region) and several expressions (sales, cost, margin etc.). One of my expressions needs some quite specific calculations, so I've put this in a macro (VB script).

What I want to do is for each row (region) that is written to the chart (there's only about 10 regions in all) I want to execute the macro but it doesn't seem to work.

I'm aware that I could create a calculated dimension as part of the chart but when I try and build the expression it won't allow me to execute a macro

Regards,

Geoff.

[Yousef_Amarneh]

can you share an example?

[rphpacheco]

I believe that you can solve this problem without the use of macros.

If you can post an example.

[#4]

Hi Guys,

thanks for the responses. I'll have to create a test Qlikview to show the issue, which will take me some time, but in the mean time are you able to answer the question please? . . .

What I want to do is for each row in a chart I want to execute a macro (call a function or sub-routine). Is this possible?

Thanks in advance.

[Anonymous]

I don't think you can call a VBScript function from a front end expression.  The best bet is to follow the advice from Raphael Pacheco  and create the appropriate expressions in the chart.  I can't imagine that it would be impossible (well, I admit in advance that my imagination is limited )

[marcus_sommer]

There are settings within the easter eggs with which you could activate vbs-functions in gui - right click on qlik-logo in bottom left corner from about qlikview - but I'm quite sure like the other guys here you didn't need it, all calculations could be done with native qlikview-functions.

Generally it should be definitely the last step to use these hidden and not supported settings. Be careful …

- Marcus

[#7]

Hi Marcus,

thanks for that.

I've changed that setting to a 1 to allow macro functions in expressions but I still seem unable to call the macro from within the expression. Are you able to give a brief, simple example please?

To explain my problem . . .

I'm having trouble getting the budget column of a sheet to show the correct value. What is happening is that the budget is being summed for customers who have sales this year, rather than the full budget for all customers.

So, for example, consider the following data . . .

Customer   Region   Sales   Budget

1                A             50        100

2                B             40        90

3                C             30        80

4                A             20        70

5                A             0          60

The budget is summed as follows . . .

Sum([Budget Budget Value])

The following chart is displayed . . .

Region   Sales   Budget

A            70        170

B            40        90

C            30        80

As there are no sales for customer 5 yet, their budget is not included in the total budget for region = A.

The following doesn't work either . . .

Sum({1}[Budget Budget Value])

No matter what I try, I can't get this to work. One solution is to have dummy records for the customer that don't yet have sales but that corrupts other things (averages, counts etc.)

So, I decided I would try and build the Sum() function via a macro. I'm having some success with this but I can't call the macro when each row is loaded, only when a selection is changed etc.

This is the reason for me asking about how do I call a macro (sub-routine/function) from within the edit expression window? In my mind, as each row of my chart is being calculated (10 rows) I would like to call a macro which creates the Sum function for the correct region and including any user selections (year, quarter, period, sales family, customer).

Thanks in advance,

Geoff.

[Anonymous]

From the description it looks like the non-zero Sales are selected somehow.  Need to see a data model, or better an example of the application to see what's going on.

The solution may involve a change in the data model.

[#9]

Hi Michael,

you said . . ..

"From the description it looks like the non-zero Sales are selected somehow."

There is no specific selection logic, but it is the non-zero sales that we are displaying.

We sum the sales and alongisde this we sum the budget.

However, the only budget records summed are those that belong to customers that have a sale, not all budget records are summed and thus the budget is being understated.

[marcus_sommer]

I didn't use these functionality in productive. Some years ago I have tested if it will generally work and it did. There are various possibilities why it could fail. My suggestion was only for a local client and not for a server environment - possibly there are similar settings but I'm not sure. Further it must be proper functions and not subs.

But I think the reason is in your data-model. It sounds that you have linked tables for sales and budget in your data-model. This won't work if some areas have sales and none budget or some have budgets and none sales then this will lead to links against null. In this case these tables must be concatenated.

- Marcus