getting a column result based on another column

[#1]

Hi all,

I'm 20 days old in qlik sense and couldn't figure out this problem.

so I have a chart table in which one of its column shows the max transaction date when a certain product is chosen by the filter .

now in that same table I have a column that displays the quantity on hand, so i need that column to shoe the quantity on hand on the exact same max date date. currently it shows all the quantities on hand for that product.

this how the table currently shows :

and this is how I want it to show based on product selection:

the script for the max date is Date (MAX([OnHand History Transaction.AsOfDate]))

any ideas?

[#2]

You can use aggr to get the results you are looking for.  I've attached a QVD with data similar to what you are describing.

Here is the data I used in my sample

LOAD * INLINE [

    PART, DATE, DESC, VALUE, QTY

    A, 6/21/2016, PRODUCTA, 8, 10

    A, 6/22/2016, PRODUCTA, 8, 15

    A, 6/23/2016, PRODUCTA, 8, 14

    A, 6/24/2016, PRODUCTA, 8, 143

    A, 6/25/2016, PRODUCTA, 8, 144

    B, 6/21/2016, PRODUCTB, 8, 24

    B, 6/22/2016, PRODUCTB, 8, 25

    B, 6/23/2016, PRODUCTB, 8, 26

    C, 6/21/2016, PRODUCTC, 8, 345

    C, 6/22/2016, PRODUCTC, 8, 14

    C, 6/23/2016, PRODUCTC, 8, 1

    C, 6/24/2016, PRODUCTC, 8, 132

];

I used the same formula you described to get max date and this for qty on hand

SUM(If(Aggr(NODISTINCT MAX(DATE), PART) = DATE, QTY))

here is the resulting table.

I have attached the sample QVD.  I hope this gets to you quickly.  My responses still need to be moderated.

[#3]

Side note.  I did not reference the max date column in my final code but the same formula of MAX(DATE).  if your scenario is more complicated and you don't wish to repeat code I can provide some code that uses variables to create reusable code for these situations.

Basically you create a variable with your max date code named vMAXDATE (or whatever you prefer)

DATE(MAX(DATE))

then your max date column code is

$(vMAXDATE)

and your qty on hand code is

SUM(If(Aggr(NODISTINCT $(vMAXDATE), PART) = DATE, QTY)) 

[#4]

Hi Matthew,

thank you for your reply ...So I did create a variable max date $(vMAXDATE) and used your second formula

SUM(If(Aggr(NODISTINCT $(vMAXDATE), PART) = DATE, QTY))

and now its giving me 0 instead of dashes that's better but still not the result I am hoping now the problem I am having is maybe because that for the exact same product it has many dates with different values of quantity on hand and I want it to display the quantity on hand corresponding to the max date.  here is a table that shows an example of the entries inside the table :

so the maximum date is 27th of September with value of -4902 so that's the value I want to show .

hope that clears the task

[#5]

that is what I want I don't know why I am getting zeros

here how I wrote it

Sum(If(Aggr(NODISTINCT $(MaxDate),([OnHand History Transaction.PartNumber])) = DATE, ([OnHand History Transaction.Total])))

and even without the extra brackets

Sum(If(Aggr(NODISTINCT $(MaxDate),[OnHand History Transaction.PartNumber]) = DATE, [OnHand History Transaction.Total]))

still got 0 as an answer

[#6]

[OnHand History Transaction.Total] is the quantity on hand column original name 

[#7]

Can you send screen shots of your dimension tab and expression tab?  I'm guessing it's just a case of you need to put all of the dimension columns in the AGGR statement where I have PART. 

Based on the sample data I provided above my result is correct right?  I had the 0 problem a lot while doing this type of aggr and it usually has to do with a dimension missing from the AGGR statment.

[#8]

Sum(If(Aggr(NODISTINCT $(MaxDate),([OnHand History Transaction.PartNumber])) = DATE, ([OnHand History Transaction.Total])))

[OnHand History Transaction.Total] is the quantity on hand column original name 

this is how the table currently looks

I have only one dimension which is Part number the rest are measures

[#9 · Accepted solution]

thanks matthew

I wasn't able to do it your way but I found an option in the quantity on hand column if it is made as dimension which gave me the same result:

as you can see from the picture this how i made it