Skip to main content

Qlik

cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
jaina
Contributor III
Contributor III
‎2020-08-19 05:02 AM

Filter Variable

Hi,

I'm trying to have a filter with the variable i have created.

Variable: ActualDeliveredQty = (sum(aggr(sum(DISTINCT [Delivery Qty]),[Sales Order],[Sales Order Item No.])))

And what I am trying to do is to create a dimension named MATCH DATA where

MATCH DATA = if([Order Qty]=$(ActualDeliveredQty),'Matched', 'Unmatched')

My goal is to filter my data  by  'Matched' and 'Unmatched'

Unfortunately, even though this formula works, when I include the dimension in my table, it became "Invalid Dimension"

How can I achieve this? Please help. Thank you.

 

 

1,343 Views
0 Likes
13 Replies
Kushal_Chawda
MVP
MVP
‎2020-08-19 07:59 AM

share sample data

411 Views
0 Likes
Kushal_Chawda
MVP
MVP
‎2020-08-19 09:32 AM

You need to include all of the table dimension in aggr ..try below

=aggr(if([Order Qty]=$(ActualDeliveredQty),'Matched','Unmtached'),
[Sales Order],[Sales Order Item No.],[Order Qty],[Delivery Qty])

1,256 Views
jaina
Contributor III
Contributor III
‎2020-08-19 09:11 PM
Author

Hello @Kushal_Chawda,

 

Thanks for this. Output are the same with the measure column.  However, when i tried to filter this out based on 'unmatched', it looks like the data became inaccurate since it is different when I export the data in excel and filter it by 'Unmatched'.. 

401 Views
0 Likes
jaina
Contributor III
Contributor III
‎2020-08-20 12:35 AM
Author

In response to Kushal_Chawda

This is what i am trying to achieve..

Order:

Sales OrderSales Order Item No.Order Qty
ORDER10000011600
ORDER10000021600

 

Delivery:

Sales OrderSales Order Item No.DeliveryDelivery Item No.Delivery QtyOrder Qty
ORDER1000001DEL100000201600
ORDER1000001DEL1900006861600
ORDER1000001DEL19000071261600
ORDER1000001DEL200000101600
ORDER1000001DEL290000181600
ORDER1000001DEL29000021251600
ORDER1000001DEL29000031201600
ORDER1000001DEL29000041071600
ORDER1000001DEL300000101600
ORDER1000001DEL3900001161600
ORDER1000001DEL39000021231600
ORDER1000001DEL39000031271600
ORDER1000001DEL39000041241600
ORDER1000001DEL39000051141600
ORDER1000001DEL400000101600
ORDER1000001DEL490000151600
ORDER1000001DEL49000021251600
ORDER1000001DEL4900003141600
ORDER1000001DEL500000101600
ORDER1000001DEL5900001381600
ORDER1000001DEL590000281600
ORDER1000001DEL59000031251600
ORDER1000001DEL59000041121600
ORDER1000001DEL5900005971600
ORDER1000002DEL500000201600
ORDER1000002DEL5900006161600
ORDER1000002DEL600000101600
ORDER1000002DEL6900001671600
ORDER1000002DEL69000021021600
ORDER1000002DEL6900003901600
ORDER1000002DEL69000041091600
ORDER1000002DEL6900005821600
ORDER1000002DEL700000101600
ORDER1000002DEL790000111600
ORDER1000002DEL7900002891600
ORDER1000002DEL800000101600
ORDER1000002DEL8900001141600
ORDER1000002DEL89000021171600
ORDER1000002DEL89000031031600
ORDER1000002DEL1000000101600
ORDER1000002DEL109000011001600
ORDER1000002DEL10900002111600
ORDER1000002DEL109000031231600
ORDER1000002DEL1100000101600
ORDER1000002DEL11900001161600
ORDER1000002DEL119000021201600
ORDER1000002DEL119000031131600
ORDER1000002DEL119000041121600
ORDER1000002DEL119000051251600
ORDER1000002DEL900000101600
ORDER1000002DEL9900001901600

 

Expected Output

Sales OrderSales Order Item No.DeliveryDelivery Item No.Delivery QtyOrder QtyActualDeliveryOrder=Actual
ORDER1000001DEL1000002016001600Matched
ORDER1000001DEL19000068616000Unmatched
ORDER1000001DEL190000712616000Unmatched
ORDER1000001DEL2000001016000Unmatched
ORDER1000001DEL2900001816000Unmatched
ORDER1000001DEL290000212516000Unmatched
ORDER1000001DEL290000312016000Unmatched
ORDER1000001DEL290000410716000Unmatched
ORDER1000001DEL3000001016000Unmatched
ORDER1000001DEL39000011616000Unmatched
ORDER1000001DEL390000212316000Unmatched
ORDER1000001DEL390000312716000Unmatched
ORDER1000001DEL390000412416000Unmatched
ORDER1000001DEL390000511416000Unmatched
ORDER1000001DEL4000001016000Unmatched
ORDER1000001DEL4900001516000Unmatched
ORDER1000001DEL490000212516000Unmatched
ORDER1000001DEL49000031416000Unmatched
ORDER1000001DEL5000001016000Unmatched
ORDER1000001DEL59000013816000Unmatched
ORDER1000001DEL5900002816000Unmatched
ORDER1000001DEL590000312516000Unmatched
ORDER1000001DEL590000411216000Unmatched
ORDER1000001DEL59000059716000Unmatched
ORDER1000002DEL5000002016001600Matched
ORDER1000002DEL59000061616000Unmatched
ORDER1000002DEL6000001016000Unmatched
ORDER1000002DEL69000016716000Unmatched
ORDER1000002DEL690000210216000Unmatched
ORDER1000002DEL69000039016000Unmatched
ORDER1000002DEL690000410916000Unmatched
ORDER1000002DEL69000058216000Unmatched
ORDER1000002DEL7000001016000Unmatched
ORDER1000002DEL7900001116000Unmatched
ORDER1000002DEL79000028916000Unmatched
ORDER1000002DEL8000001016000Unmatched
ORDER1000002DEL89000011416000Unmatched
ORDER1000002DEL890000211716000Unmatched
ORDER1000002DEL890000310316000Unmatched
ORDER1000002DEL10000001016000Unmatched
ORDER1000002DEL1090000110016000Unmatched
ORDER1000002DEL109000021116000Unmatched
ORDER1000002DEL1090000312316000Unmatched
ORDER1000002DEL11000001016000Unmatched
ORDER1000002DEL119000011616000Unmatched
ORDER1000002DEL1190000212016000Unmatched
ORDER1000002DEL1190000311316000Unmatched
ORDER1000002DEL1190000411216000Unmatched
ORDER1000002DEL1190000512516000Unmatched
ORDER1000002DEL9000001016000Unmatched
ORDER1000002DEL99000019016000Unmatched

 

and i just want it to filter based on matched and unmatched.. 

 

391 Views
0 Likes