Set Analysis Issue

[mhmmd_srf]

Hi All,

I have a requirement like:

I need count of distinct Order No, Order Line combination where count(distinct Supplier Lot Id ) > 1.

I am using below expression:

=Count({<SupplierLotID = {"=aggr(Count(distinct SupplierLotID), OrderNumber,OrderLine) > 1"}>} distinct  OrderNoLine)

Note: OrderNoLine = OrderNumber&OrderLine as OrderNoLine in Script.

For one Order No 043639, this is not working properly. It should show 1, but showing 0.

Only Oreder No 043639 and OrderLine 20 combination has multiple SupplierLotID.

I am attaching the App.

Please help to get it done.

Thanks,

Sarif

[sunny_talwar · Accepted solution]

May be this

=Count({<OrderNoLine = {"=Count(DISTINCT SupplierLotID) > 1"}>} DISTINCT  OrderNoLine)

[mhmmd_srf]

Hello Sunny,

This is working in this scenario. But I am thinking when the Combination of OrderNo and Order Line has same SupplierLot ID, then it might create issue.

Like in below scenario:

If SupplierLotID also, same for all combination. It might create issue.

Whats your thought on this?

Basically our SQL query is like below:

select distinct OrderNumber,OrderLine

from

AHPMart.FACT_PURCHASEORDER

where

DateReceived_Key >= '20170501' and

DateReceived_Key <= '20170531'

group by

OrderNumber,OrderLine

having

count(distinct SupplierLotID) >1

Please help and suggest.

Thanks,

Sarif

[Kushal_Chawda]

try this

=sum(if(aggr(Count(OrderNoLine),OrderNoLine)>1,1,0))

[thomas_vanbraba]

You can do it without set analysis.

Edit your chart and do the following.

Expression (tab 4) : Count(distinct SupplierLotID)

Dimension limits (tab 3) :

Select OrderLine

Check "Restrict which value are displayed..."

Check the second option with "Greather than", "1", "exact amount".

Disable "show others" in the options (optional).

Presentation (tab 6 optional) :

Select the expression and pick the "Hide Column" option.

[sunny_talwar]

Would you be able to share a scenario which you describe above? It's difficult to understand by reading about it. May be a sample might show the concern in a much better way