Filter Variable

[jaina]

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.

[Kushal_Chawda]

try below as calculated dimension

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

[jaina]

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]

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]

@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

[Kushal_Chawda]

Please share sample data with expected output

[jaina]

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