Data range in set analysis?

[kwl1234]

I try to filter the data by material No. list.

Material No list required:

Now, I using set analysis with hard coding to get the material No =30812* to 30815*

Sum({<[Material]={"30812*","30813*","30814*","30815*"} >}
[Value]))

I tried set number range on set analysis, but it seems not correct.

Here is the expression I tried to use to filter data by material no. list.

Sum({<[Material]={">=$(=Fm_Material_No)<=$(=To_Material_No)"} >} [Value])

but it got wrong value. How do I set the expression to perform same way on like this expression(Sum({<[Material]={"30812*","30813*","30814*","30815*"} >}
[Value])) )?

[marcus_sommer · Accepted solution]

To be able to apply a numeric comparison the values need to be numeric. In your case you may use dual-values for it, maybe in this way:

dual([Material], num#(keepchar([Material], '0123456789'))) as [Material]

Beside this I think it would be beneficial to use a hierarchy of the [Material] which may already exists and if not you could create it with subfield(), like:

subfield([Material], '_', 1) as [Material Main],
subfield([Material], '_', 2) as [Material MainSub],
subfield([Material], '_', 3) as [Material Detail],

and with [Material Main] you could also apply the showed >= <= logic.

- Marcus