Share of suppliers with Active/Expired/Missing contracts

[AlekseiN]

Hello,

I want to build a basic pie chart showing shares of suppliers with active, expired and missing contracts. As a dimension, I am using a field SupplierHasContract with possible values 'Active', 'Expired' and 'Missing' and then I am using count (distinct Supplier) to calculate respective supplier counts. Here comes the tricky part: each supplier can have multiple contracts and those suppliers that have at least one active contract (SupplierHasContract = 'Active') should not be included in 'Expired' and 'Missing' shares in the chart (in case they also have expired or missing contracts).

Could someone please advice which expression I would need to use to fulfill this condition?

Many thanks in advance!

Aleksei

[SerhanKaraer]

Hello Aleksei,

You can do it with set expression as this:

Count({<Supplier=P({<SupplierHasContract={"Missing","Expired"}>} Supplier)*P({<SupplierHasContract={"Active"}>} Supplier),SupplierHasContract={"Active"}>
+<Supplier-=P({<SupplierHasContract={"Missing","Expired"}>} Supplier)*P({<SupplierHasContract={"Active"}>} Supplier)>}

DISTINCT Supplier)

Regards,

[AlekseiN]

Thank you! This seems to work with a default view. However, when I filter by "Missing" or "Excluded" it still shows active contracts (see screenshot attached). Any idea how to get rid of them?

Regards,

Aleksei

[SerhanKaraer · Accepted solution]

You are right Aleksei. I added this default set's intersection with SupplierHasContract selection. So, here it is:

Count({(<Supplier=P({<SupplierHasContract={"Missing","Expired"}>} Supplier)*P({<SupplierHasContract={"Active"}>} Supplier),SupplierHasContract={"Active"}>
+<Supplier-=P({<SupplierHasContract={"Missing","Expired"}>} Supplier)*P({<SupplierHasContract={"Active"}>} Supplier)>)*<SupplierHasContract=P(SupplierHasContract)>}
DISTINCT Supplier)

[AlekseiN]

Yep, this fixed it. Thanks a lot for your help!