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.

[Anonymous]

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.

[#3]

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.

[sudeepkm]

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?

[#5]

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.

[marcus_sommer]

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

[#7]

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.

[marcus_sommer]

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