Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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])
try below as calculated dimension
aggr(if([Order Qty]=$(ActualDeliveredQty),'Matched', 'Unmatched'),[Sales Order],[Sales Order Item No.])
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'..
instead of variable name try below
aggr(if(sum([Order Qty])=sum(DISTINCT [Delivery Qty]),'Matched', 'Unmatched'),[Sales Order],[Sales Order Item No.])
check updated expression
hI @Kushal_Chawda still getting inaccurate data.
it has to be calculated dimension and not measure. If not working then please share sample to look at
@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
Please share sample data with expected output
@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')