topic Re: Set Analysis Issue in QlikView

Set Analysis Issue

mhmmd_srf — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Set Analysis Issue

sunny_talwar — Tue, 20 Jun 2017 08:55:33 GMT

May be this

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

Re: Set Analysis Issue

mhmmd_srf — Tue, 20 Jun 2017 09:17:15 GMT

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

Re: Set Analysis Issue

Kushal_Chawda — Tue, 20 Jun 2017 09:31:14 GMT

try this

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

Re: Set Analysis Issue

thomas_vanbraba — Tue, 20 Jun 2017 10:10:25 GMT

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.

Re: Set Analysis Issue

sunny_talwar — Tue, 20 Jun 2017 11:03:08 GMT

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