Pivot table : "manual total row" independant of dimension selections

[#1]

Hello,

In the example attached, I have succesfully created a total row aggregated by my dimensions.

But now I want my "total" row remain when I do selection on the field Category (directly on the table or by a selection list)

In the application attached here, I have two table (aggregate 1 & 2).

If I select Category values by the list, I have always the good total value on my table aggregate1, but not on the second, aggregate2.

If I select directly on the table aggregate1 dimension, I lose the total rows.

On the second table I lose always the total values when I do selections.

Could you help me? I want the total row remain on my pivot when I select Category field values.

Is it possible to have this with some formula?

If possible I prefer keep my table aggregate1 with changing expressions formula to keep the total rows independant of pivot table first dimension selections.

Thanks.

[#2]

I guess the solution is to use some set analysis clause to override dimension expression condition fields only for the total.

[#3]

Hi,

I built an example according to my understanding. Pls. have a look, it may be helpful.

Best regards,

http://quickdevtips.blogspot.com/

[#4]

Hi,

Thanks for your answer.

I didn't know this function valuelist. Is it interesting to use this instead of a QlikView table, or this is just to reduce source code?

I have already seen this sort of technique to write the expression into variables but I don't know what is the interest. If I have correctly understood, this hasn't really impact on performance.

The result you give is near of what I want. I see only one problem: When I select on the pivot table the Continent on the first dimension, the selection isn't updated.

I guess it's because you don't use the field Continent in the dimension expression.

Is it possible to have also the first dimension selection possible?