Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
timpoismans
Specialist
Specialist

Concat in Set Analysis

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

1 Solution

Accepted Solutions
sunny_talwar

May be try this

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

View solution in original post

5 Replies
vinieme12
Champion III
Champion III

Try P() instead!

can you post a sample app

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
timpoismans
Specialist
Specialist
Author

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

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.

sunny_talwar

May be try this

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

timpoismans
Specialist
Specialist
Author

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