Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
MVP
MVP

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
Highlighted
MVP
MVP

try below as calculated dimension

 

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

Highlighted
Contributor III
Contributor III

Hi @Kush 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'..

Highlighted
MVP
MVP

instead of variable name try below

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

Highlighted
MVP
MVP

check updated expression

Highlighted
Contributor III
Contributor III

hI @Kush  still getting inaccurate data. 

Highlighted
MVP
MVP

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

Highlighted
Contributor III
Contributor III

@Kush 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

 

 

Highlighted
MVP
MVP

Please share sample data with expected output

Highlighted
Contributor III
Contributor III

@Kush  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')