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: 
benvatvandata
Partner - Creator II
Partner - Creator II

How to sum 2 fields into 1 column while summing values from each column into 1 row?

Tough way to ask this, but I'll use pictures to try and explain. I'm getting the correct values for Onhand Qty, Prod Qty, and Ordered Qty (these are just taking the sum of each field ex: Sum({< LinkId = {'INVHST'} >} [Inventory Free Quantity])  --> gives me Onhand Qty)...

QV screenshot4.PNG

...but I'm getting the wrong value for Last Receipt Quantity. I want to take the sum of two fields (inventory free quantity and inventory reserved quantity), but only use the values that are associated with the last receipt date. So for example: the database that we are pulling the values from has a [Inventory Free Quantity] of 0 on 12/15/15, while having an [Inventory Reserved Quantity] of 3,000 on that same date, but on 7/31/15 [Inventory Free Quantity] has a value of 8,100 and [Inventory Reserved Quantity] has a value of 0. The formula I'm using for that column is: Sum({<[Inventory Date] = {"<=$(=date(max([Inventory Date])))"} >} [Inventory Free Quantity] + [Inventory Reserved Quantity]). However, this just takes the sum of both columns because both have a max date (giving me 8,100 + 3,000).

So when I add [Inventory Date] as a dimension I get this..

QV screenshot2.PNG

This gives me the correct value for Last Receipt Quantity for the row on '12/15/2015', but what I want is all of the values to be on 1 row.

So my desired result would be: Onhand Qty = 8,100, Prod Qty = 3,000, Ordered Qty = 10,200, and Last Receipt Quantity = 3,000.

I'm not sure if this can be done, but I'd appreciate any help!

4 Replies
Gysbert_Wassenaar

I want to take the sum of two fields (inventory free quantity and inventory reserved quantity), but only use the values that are associated with the last receipt date.

That's what the FirstSortedValue function can be used for: FirstSortedValue( [Inventory Free Quantity], -[Last Receipt Date]) will return the Inventory Free Quantity value for the last Last Receipt Date.

But only if there is only one [Inventory Free Quantity] value for the last Last Receipt Date value.


talk is cheap, supply exceeds demand
benvatvandata
Partner - Creator II
Partner - Creator II
Author

Thanks!

This worked for a few of the items, but unfortunately there are some instances where there are multiple [Inventory Free Quantity] values with the same Last Receipt Date; In that case I would like to Sum those values, any ideas?

Gysbert_Wassenaar

Something like this:

FirstSortedValue( aggr(sum([Inventory Free Quantity],[Last Receipt Date]) , -aggr([Last Receipt Date],[Last Receipt Date]))



talk is cheap, supply exceeds demand
benvatvandata
Partner - Creator II
Partner - Creator II
Author

Perfect. Thanks!