Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Jonas1
Contributor
Contributor

Set analysis expression in pivot table

Hi,

I have been trying for some time now to show the number of lost customers in a pivot table by category and class using a set analysis expression. 

 

My expression looks like this: 

=Count({$<CustomerID=P({$<YEAR={'2018'}>})-P({$<YEAR={'2019'}>})>} Distinct CustomerID)

 

I am then displaying the numbers in the following table: 

 Cat1Cat2
Class1  
Class2  

 

My expression gives the wrong values for cat1 and cat2. However, when I choose only one of the categories using a filter pane it comes out right. Why does not my expression filter by cat1 and cat2 directely in the table? 

Labels (2)
4 Replies
nisha_rai
Creator II
Creator II

Hi,

Can you share the sample data. with expected result.

sunny_talwar

Set analysis is not going to work because it is working at a chart level... try this using Aggr()

 

=Count(Aggr(
  If(Count(DISTINCT {<YEAR = {'2018'}>} CustomerID) = Count(DISTINCT {<YEAR = {'2019'}>} CustomerID)
    , CustomerID
  )
, CustomerID, Category))

 

Jonas1
Contributor
Contributor
Author

Thanks @sunny_talwar Sunny. I am unfortunately getting an error  when trying to use your measure. For me it looked like Aggr was to be placed one parenthesis further up, but I can't get it to work then either. 

 

My data is too sensitive to be shared @nisha_rai , but thanks for wanting to help 🙂 

sunny_talwar

Can you share the image of the expression with it's error message?