Repeated values in a pivot table

[schumi1980]

Dear All

I have a problem regarding a pivot table I am creating.

I have premium per customer, let's say 100k for customer A and 100k for customer B. However, I bought reinsurance with several reinsurers for customer A.

Customer A 100k

Customer B 100k

When I now add the dimension Reinsurer_Name into the pivot table, I get 100k for each reinsurer instead of just 100k once. However, I would like to look it like this:

Customer A 100k Reinsurer 1 10k

Customer A          Reinsurer 2  5k

Customer A          Reinsurer 3  5k

Customer B 100k

Total            200k                   20k

I had a solution for a static table like if(dimensionality()=0,formula,''). However, I would like to have the users adding and removing flexibly one dimension or the other (instead of Customer A it could be Local Policy 1 of Custumor A and Local Policy 2 for Customer A etc), hence the solution should be independent of any specific dimension, values should not be repeated for any dimension.

Many thanks for your help.

Best regards,

Jan

[jaibau1993]

Hi!

I can not figure out your issue. Can you please share a sample of data and a datailed explanation (maybe a picture or excel may help)?

[schumi1980]

Hi

Many thanks for your reply.

First it would look like this with any additional dimension:

	Premium	Reinsurance Premium
Company Inc	100,000	5,000
Enterprise & Co	100,000	-
Total	200000	5000

What happens when I add an additional dimension:

	Premium	Reinsurer_Name	Reinsurance Premium
Company Inc	100,000	Swiss Re	2,500
Company Inc	100,000	Hannover Re	2,000
Company Inc	100,000	Munich Re	500
Enterprise & Co	100,000		-
Total	200,000		5,000

However, I would like it to look like this:

	Premium	Reinsurer_Name	Reinsurance Premium
Company Inc	100,000	Swiss Re	2,500
Company Inc		Hannover Re	2,000
Company Inc		Munich Re	500
Enterprise & Co	100,000		-
Total	200,000		5,000

Or if I choose another dimension (dimensions are all hidden, unless activated via =SubStringCount('|' & Concat(distinct Dimension, '|') & '|', '|Reinsurer_Name|'), like Reinsurance_Type I would also like not to have

	Premium	Reinsurance_Type	Reinsurance Premium
Company Inc	100,000	XoL	2,500
Company Inc	100,000	QS	2,500
Enterprise & Co	100,000		-
Total	200,000		5,000

but I would like to have

	Premium	Reinsurance_Type	Reinsurance Premium
Company Inc	100,000	XoL	2,500
Company Inc		QS	2,500
Enterprise & Co	100,000		-
Total	200,000		5,000

The premium applies to the whole client and I would like to show that, but I would also be able to show various splits of the reinsurance premium without having the premium repeated.

Many thanks.

Jan

[jaibau1993]

Hi!

Is "Premium" a dimension or an expression?

Regards,

Jaime.

[schumi1980]

Premium & Reinsurance Premium are expressions.

[jaibau1993]

Hi!

Okay, I begin to get it. In a pivot table you can not have an expression column between dimension columns as you need so you are not using a pivot table (it seems to be a straight table).

The most similar way to solve your requirements, as far as I know, is to use a pivot table, then create the expression premium and then check "show partial sums" in Reinsurer_Name and Company.

Check the attached file!

regards,

Jaime,

[schumi1980]

Many thanks. Your solution does not seem to work in the environment I created:

I add another picture to make the whole structure clearer:

Metrics are expressions, dimensions are dimensions. Whenever the user chooses one/several dimensions and one/several metrics the pivot table below changes accordingly. Everything works fine with the exception that when I choose a dimension for reinsurance premium (let's say the names of the reinsurer) I get the premium repeated as many times as there are reinsurer names (I cannot show the data due to confidentiality reasons). 

I used your solution in a static pivot table setting, there it works fine, you can use for example an if-clause with dimensionality(). However, in the present setting I have changing dimensionalities.

Many thanks,

Jan

[jaibau1993]

Hi Jan:

You have changing dimensionalities but you can always check how many, you have to modify properly that if-clause. Example: lets suppose that in the static pivot table you writte the following expression in order to "hide" values except totals when you have 2 dimensions:

if(dimesionality() = 2, ' ', Sum(Premium))

 Now, you have to generalize it to something like

if(dimesionality() = GetSelectedCount([Dimensions list]), ' ', Sum(Premium))

Note that this will work if you have the "Always fully expanded" box checked!

Regards,

Jaime.