Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
schumi1980
Contributor III
Contributor III

Repetition of results in Pivot table

Dear All

I have a small question which drives me crazy: I have a pivot and get the following results:

PictureZ.jpg

However, the premium should only be 100, however, as the claims are matched to the policy, if I add the claims number as a dimension the premium numbers multiply times the number of claims.

Is it possible to get a pivot table that does not multiply the premium and might look like that:

PictureZZ.jpg

Many thanks.

Best regards,

Jan

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

An alternatively might be to divide the sum through the count which would result in 25 of row-level and 100 in total. Another way might be to apply the sum only by rowno()=1.

- Marcus  

View solution in original post

6 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Couldn't you just take the average like: avg(Premium) or maybe avg(aggr(sum(Premium), [Policy Number])) ?

- Marcus

schumi1980
Contributor III
Contributor III
Author

That would work for me but our customers would be confused.

sudeepkm
Specialist III
Specialist III

would it be helpful for the end users if you display the Claim info as a concatenated string separated by a '|' or other  separator. In that way it will be only one row at policy level and claims related to that policy being displayed together concatenated.

schumi1980
Contributor III
Contributor III
Author

Unfortunately I need to match a certain format manually done today in excel. I would need the table to look exactly as above.

marcus_sommer
MVP & Luminary
MVP & Luminary

An alternatively might be to divide the sum through the count which would result in 25 of row-level and 100 in total. Another way might be to apply the sum only by rowno()=1.

- Marcus  

View solution in original post

schumi1980
Contributor III
Contributor III
Author

The rowno()=1 did the trick.

Many thanks.

Jan