Qlik Community

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
QLIKWORLD LIVE! MAY 16 - 19TH, EARLY BIRD DISCOUNTS! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
AG-gugelbisolutions
Contributor III
Contributor III

Pivot Table and TOTAL

Hi there, I recently noticed a bit strange behavior while working on a client's app using Qlik Sense (November 2019).

We have a pivot table with two dimensions: Agent and Customer. There are many expressions but one in particular is tricky: Last Year Invoice Amount of the customer regardless the agent. The point is that a Customer may pass from an agent to another some point in time last year and we want to see its overall last year performance, right beside the number that tell us what the current agent was able to do with that customer last year. 

What I came up with, at the end, is as follows:

if( dimensionality() = 2,
sum( TOTAL<[COMPANIES.COMPANY1]> {$< [DOCDATE.autoCalendar.Year]={$(vAP)}, DOCDATE={"<=$(vAPMaxDataYTD)"} $(vCampiEsclusiSetAnalysis_AP) >}
aggr(
Sum( { $< [DOCDATE.autoCalendar.Year]={$(vAP)}, DOCDATE={"<=$(vAPMaxDataYTD)"} $(vCampiEsclusiSetAnalysis_AP) >} [SALESAMMOUNT] )
,[COMPANIES.COMPANY1] ) )
)

It seemed to be working fine but then I realized that there is a condition: Customer level has to be open for both the old and the new agent on order to make the TOTAL work!

To me it sounds like a bug, but it also could be an unknown (to me) behavior of the pivot table.

A reverted back to a straight table and it works just fine

 

Labels (2)
0 Replies