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
