Qlik Community

App Development

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

Announcements
Qlik Insider: SaaS capabilities for Data Integration and Analytics, May 25th! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
luizcdepaula
Creator II
Creator II

Set Analysis - count orders with two different load dates

Hello Experts,

I am trying to build a KPI to show the number of sales orders that have more than one Load Date, which we should never have multiple load dates per order.

Currently I have a report using the logic below, and it works, because I have SALES_ID as a dimension on the report. However, if I want to count the amount of order with multiple load dates, it is not working for the KPI object.

Sum(Aggr(Count(distinct [Load Date]),SALES_ID))

Any suggestions?

Thanks,

Luiz

Labels (2)
1 Solution

Accepted Solutions
Stoyan_Terziev
Partner
Partner

Hello Luizc,

 

You can try doing this:
Sum(Aggr(If(Count(DISTINCT [Load Date])>1, 1, 0), SALES_ID))

 

This should generate the count of all such transactions since it will sum the number of 1's or 0's generated after a check for each individual transaction.

Basically you can should be able to combine this measure with any dimension you want.

 

Please let me know if it works.


Kind regards,

S.T.

View solution in original post

2 Replies
Stoyan_Terziev
Partner
Partner

Hello Luizc,

 

You can try doing this:
Sum(Aggr(If(Count(DISTINCT [Load Date])>1, 1, 0), SALES_ID))

 

This should generate the count of all such transactions since it will sum the number of 1's or 0's generated after a check for each individual transaction.

Basically you can should be able to combine this measure with any dimension you want.

 

Please let me know if it works.


Kind regards,

S.T.

luizcdepaula
Creator II
Creator II
Author

Hi Stoyan,

I believe your solution works. It seems simple but I could not get it right. I was using the logic below with the IF function in the wrong place, so it was still summing all distinct Load Dates, regardless of the Sales_ID.

If(Sum(Aggr(Count(distinct [Load Date]),SALES_ID))=1,0,Aggr(Count(distinct [Load Date]),SALES_ID))

Also, I came up with another set analysis that seems to work as well. I will just be able to verify when an order with multiple Load Dates comes into the system. Find my logic below

Count({<SALES_ID={"=Aggr(Count(distinct [Load Date]),SALES_ID)>1"}>}SALES_ID)

I will mark your solution as right as it worked as well.

Thank you for the quick response.

Luiz