5 Replies Latest reply: Aug 22, 2017 7:08 AM by Sunny Talwar RSS

    Concat in Set Analysis

    Tim Poismans

      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

        • Re: Concat in Set Analysis
          Vineeth Pujari

          Try P() instead!

           

          can you post a sample app

           

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

            • Re: Concat in Set Analysis
              Tim Poismans

              Sum({<[%Document Number]=P(1{Product='[B] 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

              OrderLine example.png

               

              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.