Set Analysis - count orders with two different load dates

[luizcdepaula]

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

[Stoyan_Terziev · Accepted solution]

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]

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