Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
May be this
=Count({<OrderNoLine = {"=Count(DISTINCT SupplierLotID) > 1"}>} DISTINCT OrderNoLine)
May be this
=Count({<OrderNoLine = {"=Count(DISTINCT SupplierLotID) > 1"}>} DISTINCT OrderNoLine)
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
try this
=sum(if(aggr(Count(OrderNoLine),OrderNoLine)>1,1,0))
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.
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