Keep original values visible after selection

[pvanderkolk]

Dear Qlikers,

In the attached Excel sheet you find three straight tables with a different selection. It is showing the inventory on articles where a customer has put an order against. When I'm selecting a Containerno I get extra stock (see column Container) it logically shows a value and amount for the articles that are available in the selected container, like the second table Output.

However, I want my straight table as shown in the below straight table Desired output. So the original expression values shouldn't dissapear and the extra stock from the container should also be visible in the columns Value and Deliverable. I'm using below formula and it works when I don't select any container, but will turn into the second table in the Excel attachment when I do select a container.

=Sum(Aggr(

If(sum({<ArtHasInventory={1}>}OrdAmountOrdered-OrdAmountDelivered)<=

If(GetSelectedCount(Containerno)>=1,sum({<ArtHasInventory={1}>}Container),0)+

sum({<ArtHasInventory={1}>}if(Stock<0,0,Stock)),

sum({<ArtHasInventory={1},Containerno=>}(OrdAmountOrdered-OrdAmountDelivered)*(Price-(Price*Discount/100)),

(If(GetSelectedCount(Containerno)>=1,sum({<ArtHasInventory={1},Containerno=>}Container),0)+sum({<ArtHasInventory={1},Containerno=>}if(Stock<0,0,Stock)))*(Price-(Price*Discount/100)))

,Order,Article))

OrdAmountOrdered-OrdAmountDelivered is the formula of the column To Deliver.

Is this problem solvable (without using Alternative State)?

Thanks in advance!

Pim

[pvanderkolk]

Yes Sunny, only those six articles are available in the two containers. The field isn't a column in the table, but will only be used as a list box. But when selecting (one of) the containers, the value and deliverables should increase and be added to the totals that are covered by the warehouse stock

[sunny_talwar]

I am not entirely sure I understand your business... but is this what you want?

Without selection

With selection

[pvanderkolk]

Yes, the far right column is doing what the columns Value and Deliverable have to do too: when you select container 55685... the three articles in that container change from 0 to the new value. The articles that aren't in one of the containers should't change and must retain their values. When I added the If-function to the column far right, I got the same issue as in the columns Value and Deliverables..

[sunny_talwar]

Sorry, my bad... I inquired about the wrong expression... but you description to my incorrect inquiry helped a lot (I think)... is this what you want?

[pvanderkolk]

Almost Sunny! I already had the same output for the column far right, that's why I've added that to my sample as well. But the two target columns are getting closer the correct answer as well! The only thing is that it shows the new values for both containers, even if select only one container. In the latter case only three values should increase instead of six.

[sunny_talwar · Accepted solution]

How about now?

[pvanderkolk]

As usual you've hit the nail on the head! This was exactly what I needed Sunny!

Played a lot with the field Containernummer in the Set Analysis, but I couldn't execute it with the right result. Besides that I haven't put this field between the Sum and Aggr function, so I was doomed to fail anyways

Could you please remove your two qvw's from the post (no password needed anymore).

Thanks again for the great help Sunny, much appreciated!!

[sunny_talwar]

Qvw removed... images can stay?

[pvanderkolk]

No problem!