Chart with one dimension linked to 2 fields

Thu, 25 Jun 2015 08:04:34 GMT

Hello fellow QV users.

I have an issues with the following.

To simplify things, let's say that I have 2 tables:

- One is about projects and look like this : Project ID, Program ID, Year1, Estimated costs,

- The other is about programs and look like this : Program ID, Year2, Estimated budget

So the first table provide the estimated costs for each year (2015,2016...) of the project and link the project to a program.

The second table give the yearly budget from a program perspective.

I want to display a chart presenting for each year the sum of projects costs, the sum of program budget and the delta between them and also see the total to the left.

My issue is that I cannot find a way to link the years into one dimension without messing with the "show total" option in the dimension limit tab.

If i choose Year1 as a dimension, I tried using if(Year1='2015',sum({$<year2=2015>} [Estimated budget] to get the related program budget, but then using the "Show total" option in dimension limits will not work anymore.

Any idea ?

Regards.

topic Chart with one dimension linked to 2 fields in QlikView

Chart with one dimension linked to 2 fields