Set analysis - excluding values

[timpoismans]

Hi all.

Consider the following situation:

Every Document Number has Items. Every Item can have multiple Product Groups.

The customer has a number of Products they view as "Super articles". But some Items are built out of more than one "Super article"

For example, Item 27 in the picture. After filtering of the "Super articles" the Item is listed under the 500-584 Product Group, but it's listed under the BA4 Product Group as well.

We only want to see it under the BA4 Product Group, so I thought set analysis like this:

     Sum({<[Product Group] = {">=500<=584"} - {'BA4'}>}

would stop Item 27 from showing in the 500-584 Product Group. But it seems I'm completely wrong, as Item 27 is still showing here.

Any advice would be appreciated,

Thanks in advance!

Kind regards,

Tim Poismans

[timpoismans]

The solution works partially.

If I select a Document Number, it shows the items correctly, but when I don't have anything selected and expect to see all the Document Numbers with the correct items, it shows me nothing at all.

And if I select a date range, e.g. the client's fiscal year (which starts in July), it only shows the lines for a specific date.

[antoniotiman]

If(GetSelectedCount(DocNr) > 0,
Sum({<[Product Group] = {">=500<=584"} ,Item=E({<[Product Group]={'BA4'}>})>} Amount),
Sum(Amount))

Can You share date which should show 2nd issue ?

[dennisfassbende]

Hi all,

Set the "-" after "="

Sum({< [Product Group]={">=500<=584"} , [Product Group]=-{"BA4"}>}Amount)

[msvanfeldt]

Try this one,

Sum({$-$<Item = {"=Count({$<ProductGroup = {[>=500<=584],'BA4'}>}distinct ProductGroup) >= 2"}>}Amount)

This will exclude all items with two or more product groups within your range.

[timpoismans]

Splitting the two filters on the same field does not work sadly. Shows me more than I want to see.

[timpoismans]

But in this context, if an item only has ProductGroup 'BA4', it'll show it as well, no?

[timpoismans]

What I meant was that if I have a DocNr selected, it shows me the correct items. But when I have no DocNr selected, but want to see all the DocNr where the filter applies, it shows me nothing.
It's not that if I haven't selected anything, that i want to see everything. I want to see the DocNr's that still pass the filter.

I sadly can't you give you data on the 2nd issue, that would require me taking data out of the application.

Most likely it's a negative result of this

     Item=E({<[Product Group]={'BA4'}>})

part of the code. Perhaps it has difficulties in showing the correct values with multiple DocNr’s?