topic Re: Is it possible to run a macro for each row of a chart (table) in QlikView

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

Fri, 01 Aug 2014 14:20:08 GMT

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.

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

Yousef_Amarneh — Fri, 01 Aug 2014 14:23:21 GMT

can you share an example?

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

rphpacheco — Fri, 01 Aug 2014 15:12:38 GMT

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

If you can post an example.

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

Sat, 02 Aug 2014 17:18:00 GMT

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.

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

Anonymous — Sat, 02 Aug 2014 17:39:17 GMT

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 )

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

marcus_sommer — Sun, 03 Aug 2014 08:49:37 GMT

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

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

Sun, 03 Aug 2014 14:37:52 GMT

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.

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

Anonymous — Sun, 03 Aug 2014 15:53:07 GMT

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.

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

Sun, 03 Aug 2014 16:02:02 GMT

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.

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

marcus_sommer — Sun, 03 Aug 2014 16:42:14 GMT

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

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

Anonymous — Sun, 03 Aug 2014 16:58:57 GMT

Geoff

Attached is a very simple qvw with a single table loaded via an Inline Load and it works fine.

I suspect your data model has more than one table in it though.. Could you post an image of your data model.

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

Mon, 04 Aug 2014 10:57:23 GMT

Hi Bill,

thanks for that.

Apologies but my sample data was poor and misleading. First of all, I should have explained that I have a sales file, which can contain many records per customer, and a budget file, which contains 1 record per customer. So, for example, consider the following data . . .

Customer Region Sales

1 A 50

1 A 45

2 B 40

3 C 30

4 A 20

Customer Budget

1 100
2 90

3 80

4 70

5 60

Note, no sales yet for customer 5. Now, when I present the sum of the sales and the sum of the budget, only budget values to which we have a sale are summed.

Regards,

Geoff.

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

sudeepkm — Mon, 04 Aug 2014 11:14:23 GMT

as per your sample data if I create a qvw with simply associating Sales and budget base don customer id then I can find proper results in the chart. Can you please elaborate your problem a little bit?

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

Tue, 05 Aug 2014 12:22:27 GMT

Hi Sudeep/all,

please review the attached simple .qvw.

If you look at the Region table you will see the following . . .

Region Sales Budget
150 1200
A 80 55

B            40        15
C            30        20
-            0          1110

What I assume is happening is that only the budget data that has matching sales data are being summed - In other words, for customer 1 (region A) , periods 1, 2, and 3 are summed because there are sales records for those periods. For customer 2 (region B), only period 1 is summed etc.

What I need to be able to do is . . .

If there are no selections, show the full budget for each region, even if customers within that region have no sales data

If there are selections - same as above but take in to account the selections (so, if a user selects period 1, I want to see the full budget for period 1 for that region.

Regards,

Geoff.

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

marcus_sommer — Tue, 05 Aug 2014 12:57:27 GMT

Your data-model isn't suitable then there are synthetic keys and generellay how above mentioned to link these both tables will cause problems if some ares have no mathcing together. You need to concatenate these tables:

Data:

Load * From Sales;

concatenate

Load * From Budget;

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/16/synthetic-keys

- Marcus

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

Tue, 05 Aug 2014 13:19:06 GMT

Hi Marcus,

in my actual .qvw I do not have synthetic keys. I have changed the attached .qvw to also not have synthetic keys and I still have the same issue.

If I were to concatenate the tables then won't I have a problem whereby my budget is over-stated because each sales row for period 1 will contain period 1's budget and this will be accumulated many times?

Regards,

Geoff.

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

marcus_sommer — Tue, 05 Aug 2014 14:27:29 GMT

Hi Geoff,

we have had the same challange to have sales-data with many rows per date and budget-data with one row per month. The only what you need is to create a date form you time-period-field:

Data:

Load Customer, Date, Sales From Sales;

concatenate

Load Customer, makedate(Year, Month, 1) as Date, Budget From Budget;

and this linked to a master-calendar and further dimension-tables.

- Marcus