Strange behavior with pivot table and calculated dimension as second dimension

wayne-a — Thu, 02 May 2019 12:18:59 GMT

Hi, I'm using a pivot table with a calculated second dimension. In order to get the sorting by the measure I created the second dimension as a calculated dimension as detailed in this community entry. https://community.qlik.com/t5/Qlik-Sense-App-Development/Order-pivot-table-by-expression/td-p/145674...

This seemed to work great but when verifying the information I started seeing some strange behavior. Where I used the calculated dimension I see the calculated dimension listed twice but the measure is actually for a different value. Here is the calculated dimension, it is the second dimension. The first dimension [Billing Company] is not calculated.

=Dual(Category, Aggr(Sum(Amount), [Billing Company], Category))

And here is the sort expression. It is second in the sort order, first is [Billing Company] sorted by expression as Sum(Amount)

Aggr(Sum(Amount), [Billing Company], Category)

In the example below, the Category "Handling Fee" is listed twice. Only filtering is done on the dates.

Category value listed twice with calculated dimension

But the $7.00 amount is actually for a different category, "Kit Fee". When I use the Category as a native field instead of the calculated dimension it looks like this.

Not using calculated dimension

And what's really strange is that if I filter by the company on the pivot table with the calculated dimension it displays correctly!

Calculated dimension with Billing Company filter applied

Thanks in advance for any help / insight.

topic Strange behavior with pivot table and calculated dimension as second dimension in App Development

Strange behavior with pivot table and calculated dimension as second dimension