topic Set Analysis - count orders with two different load dates in App Development

Set Analysis - count orders with two different load dates

luizcdepaula — Sat, 16 Nov 2024 04:07:36 GMT

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

Re: Set Analysis - count orders with two different load dates

Stoyan_Terziev — Wed, 13 Nov 2019 21:03:19 GMT

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.

Re: Set Analysis - count orders with two different load dates

luizcdepaula — Wed, 13 Nov 2019 21:40:44 GMT

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