Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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.

16 Replies
Yousef_Amarneh
Partner
Partner

can you share an example?

Yousef Amarneh
rphpacheco
Creator III
Creator III

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

If you can post an example.

Not applicable
Author

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.

mov
Champion III
Champion III

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
MVP & Luminary
MVP & Luminary

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 …

easter_egg.jpg

- Marcus

Not applicable
Author

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.

mov
Champion III
Champion III

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.

Not applicable
Author

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
MVP & Luminary
MVP & Luminary

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