Concat in Set Analysis

[timpoismans]

Hi all

Trying to cook something up for a client.

The idea:

The client has a couple of products, lets call them superproducts. On their orders, they list a total price per orderline, but each orderline contains a few products, some of which are these superproducts. But the [#Net Amount] for these sub order lines are listed as 0.

So the client wanted a list of all the order numbers where a superproduct was part of the sub orderlines and with the [#Net Amount] per order.

So far so good, I got the list by using an If-test on the Document Number (=Order Number)

Then he wanted a graph which shows the [#Net Amount] per month in a fiscal year. So I had to turn my If-test into set analysis and this is what I came up with:

Sum({<[%Document Number]={"=$(=Concat(Aggr(If(WildMatch(Concat(Product), '* Screenline 22C-U *')>0,[%Document Number],Null()),[%Document Number]),';'))"}>}[# Net Amount Part])

Now, If I use the

Concat(Aggr(If(WildMatch(Concat(Product), '* Screenline 22C-U *')>0,[%Document Number],Null()),[%Document Number]),';')

in a text box, it gives me a ; list, which for some reason doesn't work with set analysis?

Any help regarding the formula or syntax is appreciated, thanks in advance!

Edit: When selected one Document Number, the formula works, it's when I try multiple, it fails. Perhaps something wrong with how the numbers are listed?

Regards,

Tim Poismans

[vinieme12]

Try P() instead!

can you post a sample app

P() &amp; E() and where do you use them?

[timpoismans]

Sum({<[%Document Number]=P(1{Product=' 22SL Screenline 22C-U  (22 mm)'} [%Document Number])>}[# Net Amount Part])

Is what I came up with using P, but it gives me an error in expression. Not too familiar with the use of it though.

Cannot post a sample app, working on the client's hub so don't have access to it. But I do have an example image

The column 'Item' stands for the Orderline. Only the first product in the orderline has the [#Net Amount], the underlying products don't. But one of those products is a superproduct, so I need the [#Net Amount] of that Order, but I can't just select on it, as it shows 0.00 for that superproduct under [#Net Amount].

So the basic idea is that I need the [#Net Amount] of an order, if that order contains a superproduct.

[sunny_talwar · Accepted solution]

May be try this

Sum({<[%Document Number] = p({<Product = {'* Screenline 22C-U *'}>})>}[# Net Amount Part])

[timpoismans]

Thank you very much, Sunny. Solved the issue!

Could you explain me why

Sum({<[%Document Number]=P(1{Product=' 22SL Screenline 22C-U  (22 mm)'} [%Document Number])>}[# Net Amount Part])

the highlighted parts aren't needed?

[sunny_talwar]

You mean these

Sum({<[%Document Number] = p({1<Product = {'* Screenline 22C-U *'}>} [%Document Number])>}[# Net Amount Part])

The field name within p() is optional, you can add it if you want. On the other hand 1 depends on your requirement where if you want to ignore selections in the inner set analysis, you can add it, else you don't need it. In the end it all depends on your requirement