Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
schumi1980
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
jaibau1993
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)?

schumi1980
Contributor III
Contributor III
Author

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

jaibau1993
Partner
Partner

Hi!

Is "Premium" a dimension or an expression?

Regards,

Jaime.

schumi1980
Contributor III
Contributor III
Author

Premium & Reinsurance Premium are expressions.

jaibau1993
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,

schumi1980
Contributor III
Contributor III
Author

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

 

jaibau1993
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.