Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jaina
Contributor III
Contributor III

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 Solution

Accepted Solutions
Kushal_Chawda

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])

View solution in original post

13 Replies
Kushal_Chawda

try below as calculated dimension

 

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

jaina
Contributor III
Contributor III
Author

Hi @Kushal_Chawda thanks for this, but it looks like the data became inaccurate. 

some rows which are actually matched became unmatched and vise versa. Tried also to filter but i got an error says 'invalid command'..

Kushal_Chawda

instead of variable name try below

aggr(if(sum([Order Qty])=sum(DISTINCT [Delivery Qty]),'Matched', 'Unmatched'),[Sales Order],[Sales Order Item No.])

Kushal_Chawda

check updated expression

jaina
Contributor III
Contributor III
Author

hI @Kushal_Chawda  still getting inaccurate data. 

Kushal_Chawda

it has to be calculated dimension and not measure. If not working then please share sample to look at

jaina
Contributor III
Contributor III
Author

@Kushal_Chawda yes i added it as calculated dimension named as Match Data column,

while Order = Actual Delivered Qty column is a calculated measure

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

which serve as my basis if data based on the provided formula above are correct

jaina_0-1597834206993.png

 

 

Kushal_Chawda

Please share sample data with expected output

jaina
Contributor III
Contributor III
Author

@Kushal_Chawda  expected output should be the same with Order = Actual Delivered Qty column since that column is a measure and i cannot filter out based on its value ('matched' and 'unmatched')