Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
IMPORTANT: Upcoming LEF Database Maintenance, Oct. 3rd - SEE DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

Repeated values in a pivot table

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

 

Labels (2)
7 Replies
Highlighted
Partner
Partner

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)?

Highlighted
Contributor III
Contributor III

Hi

Many thanks for your reply.

First it would look like this with any additional dimension:

 PremiumReinsurance Premium
Company Inc         100,000                                   5,000
Enterprise & Co         100,000                                          -  
Total2000005000

 

What happens when I add an additional dimension:

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

 

However, I would like it to look like this:

 PremiumReinsurer_NameReinsurance Premium
Company Inc   100,000Swiss 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

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

 

but I would like to have

 PremiumReinsurance_TypeReinsurance Premium
Company Inc   100,000XoL                                   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

Highlighted
Partner
Partner

Hi!

Is "Premium" a dimension or an expression?

Regards,

Jaime.

Highlighted
Contributor III
Contributor III

Premium & Reinsurance Premium are expressions.

Highlighted
Partner
Partner

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,

Highlighted
Contributor III
Contributor III

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

I add another picture to make the whole structure clearer:

pivot.PNG

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

 

Highlighted
Partner
Partner

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.