topic Re: How to sum 2 fields into 1 column while summing values from each column into 1 row? in QlikView

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

benvatvandata — Thu, 17 Dec 2015 16:47:59 GMT

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)...

...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..

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!

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

Gysbert_Wassenaar — Thu, 17 Dec 2015 19:03:42 GMT

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.

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

benvatvandata — Thu, 17 Dec 2015 20:22:33 GMT

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?

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

Gysbert_Wassenaar — Fri, 18 Dec 2015 17:42:56 GMT

Something like this:

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

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

benvatvandata — Fri, 18 Dec 2015 18:42:50 GMT

Perfect. Thanks!