Problem with calculated dimension and sub-total

nicolas_martin — Fri, 10 Oct 2014 08:00:44 GMT

Hello,

I have the following data:

Ref:

LOAD * INLINE [

Country, Color, Ref

France, Blue, 1000

France, Red, 2000

France, Green, 3000

France, Yellow, 4000

];

Dealer:

LOAD * INLINE [

Color, Dealer, Price

Blue, Aaa, 10

Red, Aaa, 11

Green, Aaa, 12

Yellow, Aaa, 13

Blue, Bbb, 20

Red, Bbb, 21

Green, Bbb, 22

Yellow, Bbb, 23

];

Where "Ref" is a reference value for each Color.

I want to make a graph for each Dealer and show:

- the price they sold the Color item

- the Ref of the Color item

- the subtotal of the Ref

As the Ref is the same for every Dealer, I want to put it in a calculated dimension:

=aggr(
sum(Ref)
, Color)

OK, No problem, I still can have the "subtotal of the Ref".

Now, I'ld like to add the "Country" dimension in my graph.

For the "subtotal of the Ref", my expression become:

sum(total <Country> Ref)

No problem with the first type of chart:

My subtotal is 10000 on each line, that is what I expect.

But when I want the Ref as calculated dimension (to have it in only 1 column):

=aggr(
sum(Ref)
, Color, Country)

My subtotal becomes wrong:

And I don't find a way to have the "10000" value

Can you explain me why, and how I can have the "10000" value on each line?

Thank you.

topic Problem with calculated dimension and sub-total in QlikView