topic Re: Filter Variable in App Development

Filter Variable

jaina — Wed, 19 Aug 2020 09:02:45 GMT

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.

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 09:18:16 GMT

try below as calculated dimension

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

Re: Filter Variable

jaina — Wed, 19 Aug 2020 09:45:53 GMT

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'..

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 09:55:59 GMT

instead of variable name try below

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

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 09:56:14 GMT

check updated expression

Re: Filter Variable

jaina — Wed, 19 Aug 2020 10:11:45 GMT

hI @Kushal_Chawda still getting inaccurate data.

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 10:39:13 GMT

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

Re: Filter Variable

jaina — Wed, 19 Aug 2020 10:56:57 GMT

@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

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 11:25:49 GMT

Please share sample data with expected output

Re: Filter Variable

jaina — Wed, 19 Aug 2020 11:54:09 GMT

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

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 11:59:03 GMT

share sample data

Re: Filter Variable

Kushal_Chawda — Wed, 19 Aug 2020 13:32:42 GMT

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

Re: Filter Variable

jaina — Thu, 20 Aug 2020 01:11:42 GMT

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'..

Re: Filter Variable

jaina — Thu, 20 Aug 2020 04:35:54 GMT

This is what i am trying to achieve..

Order:

Sales Order	Sales Order Item No.	Order Qty
ORDER1	000001	1600
ORDER1	000002	1600

Delivery:

Sales Order	Sales Order Item No.	Delivery	Delivery Item No.	Delivery Qty	Order Qty
ORDER1	000001	DEL1	000002	0	1600
ORDER1	000001	DEL1	900006	86	1600
ORDER1	000001	DEL1	900007	126	1600
ORDER1	000001	DEL2	000001	0	1600
ORDER1	000001	DEL2	900001	8	1600
ORDER1	000001	DEL2	900002	125	1600
ORDER1	000001	DEL2	900003	120	1600
ORDER1	000001	DEL2	900004	107	1600
ORDER1	000001	DEL3	000001	0	1600
ORDER1	000001	DEL3	900001	16	1600
ORDER1	000001	DEL3	900002	123	1600
ORDER1	000001	DEL3	900003	127	1600
ORDER1	000001	DEL3	900004	124	1600
ORDER1	000001	DEL3	900005	114	1600
ORDER1	000001	DEL4	000001	0	1600
ORDER1	000001	DEL4	900001	5	1600
ORDER1	000001	DEL4	900002	125	1600
ORDER1	000001	DEL4	900003	14	1600
ORDER1	000001	DEL5	000001	0	1600
ORDER1	000001	DEL5	900001	38	1600
ORDER1	000001	DEL5	900002	8	1600
ORDER1	000001	DEL5	900003	125	1600
ORDER1	000001	DEL5	900004	112	1600
ORDER1	000001	DEL5	900005	97	1600
ORDER1	000002	DEL5	000002	0	1600
ORDER1	000002	DEL5	900006	16	1600
ORDER1	000002	DEL6	000001	0	1600
ORDER1	000002	DEL6	900001	67	1600
ORDER1	000002	DEL6	900002	102	1600
ORDER1	000002	DEL6	900003	90	1600
ORDER1	000002	DEL6	900004	109	1600
ORDER1	000002	DEL6	900005	82	1600
ORDER1	000002	DEL7	000001	0	1600
ORDER1	000002	DEL7	900001	1	1600
ORDER1	000002	DEL7	900002	89	1600
ORDER1	000002	DEL8	000001	0	1600
ORDER1	000002	DEL8	900001	14	1600
ORDER1	000002	DEL8	900002	117	1600
ORDER1	000002	DEL8	900003	103	1600
ORDER1	000002	DEL10	000001	0	1600
ORDER1	000002	DEL10	900001	100	1600
ORDER1	000002	DEL10	900002	11	1600
ORDER1	000002	DEL10	900003	123	1600
ORDER1	000002	DEL11	000001	0	1600
ORDER1	000002	DEL11	900001	16	1600
ORDER1	000002	DEL11	900002	120	1600
ORDER1	000002	DEL11	900003	113	1600
ORDER1	000002	DEL11	900004	112	1600
ORDER1	000002	DEL11	900005	125	1600
ORDER1	000002	DEL9	000001	0	1600
ORDER1	000002	DEL9	900001	90	1600

Expected Output

Sales Order	Sales Order Item No.	Delivery	Delivery Item No.	Delivery Qty	Order Qty	ActualDelivery	Order=Actual
ORDER1	000001	DEL1	000002	0	1600	1600	Matched
ORDER1	000001	DEL1	900006	86	1600	0	Unmatched
ORDER1	000001	DEL1	900007	126	1600	0	Unmatched
ORDER1	000001	DEL2	000001	0	1600	0	Unmatched
ORDER1	000001	DEL2	900001	8	1600	0	Unmatched
ORDER1	000001	DEL2	900002	125	1600	0	Unmatched
ORDER1	000001	DEL2	900003	120	1600	0	Unmatched
ORDER1	000001	DEL2	900004	107	1600	0	Unmatched
ORDER1	000001	DEL3	000001	0	1600	0	Unmatched
ORDER1	000001	DEL3	900001	16	1600	0	Unmatched
ORDER1	000001	DEL3	900002	123	1600	0	Unmatched
ORDER1	000001	DEL3	900003	127	1600	0	Unmatched
ORDER1	000001	DEL3	900004	124	1600	0	Unmatched
ORDER1	000001	DEL3	900005	114	1600	0	Unmatched
ORDER1	000001	DEL4	000001	0	1600	0	Unmatched
ORDER1	000001	DEL4	900001	5	1600	0	Unmatched
ORDER1	000001	DEL4	900002	125	1600	0	Unmatched
ORDER1	000001	DEL4	900003	14	1600	0	Unmatched
ORDER1	000001	DEL5	000001	0	1600	0	Unmatched
ORDER1	000001	DEL5	900001	38	1600	0	Unmatched
ORDER1	000001	DEL5	900002	8	1600	0	Unmatched
ORDER1	000001	DEL5	900003	125	1600	0	Unmatched
ORDER1	000001	DEL5	900004	112	1600	0	Unmatched
ORDER1	000001	DEL5	900005	97	1600	0	Unmatched
ORDER1	000002	DEL5	000002	0	1600	1600	Matched
ORDER1	000002	DEL5	900006	16	1600	0	Unmatched
ORDER1	000002	DEL6	000001	0	1600	0	Unmatched
ORDER1	000002	DEL6	900001	67	1600	0	Unmatched
ORDER1	000002	DEL6	900002	102	1600	0	Unmatched
ORDER1	000002	DEL6	900003	90	1600	0	Unmatched
ORDER1	000002	DEL6	900004	109	1600	0	Unmatched
ORDER1	000002	DEL6	900005	82	1600	0	Unmatched
ORDER1	000002	DEL7	000001	0	1600	0	Unmatched
ORDER1	000002	DEL7	900001	1	1600	0	Unmatched
ORDER1	000002	DEL7	900002	89	1600	0	Unmatched
ORDER1	000002	DEL8	000001	0	1600	0	Unmatched
ORDER1	000002	DEL8	900001	14	1600	0	Unmatched
ORDER1	000002	DEL8	900002	117	1600	0	Unmatched
ORDER1	000002	DEL8	900003	103	1600	0	Unmatched
ORDER1	000002	DEL10	000001	0	1600	0	Unmatched
ORDER1	000002	DEL10	900001	100	1600	0	Unmatched
ORDER1	000002	DEL10	900002	11	1600	0	Unmatched
ORDER1	000002	DEL10	900003	123	1600	0	Unmatched
ORDER1	000002	DEL11	000001	0	1600	0	Unmatched
ORDER1	000002	DEL11	900001	16	1600	0	Unmatched
ORDER1	000002	DEL11	900002	120	1600	0	Unmatched
ORDER1	000002	DEL11	900003	113	1600	0	Unmatched
ORDER1	000002	DEL11	900004	112	1600	0	Unmatched
ORDER1	000002	DEL11	900005	125	1600	0	Unmatched
ORDER1	000002	DEL9	000001	0	1600	0	Unmatched
ORDER1	000002	DEL9	900001	90	1600	0	Unmatched

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