Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
mhmmd_srf
Creator II
Creator II

Set Analysis Issue

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

1 Solution

Accepted Solutions
sunny_talwar

May be this

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

View solution in original post

5 Replies
sunny_talwar

May be this

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

View solution in original post

mhmmd_srf
Creator II
Creator II
Author

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

Kush
MVP
MVP

try this

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

thomas_vanbraba
Contributor
Contributor

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