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: 
thepiapus
Contributor II
Contributor II

Set analysis max date

I have a lines of sales and a price list on a specific date. The sales row has a field [Posting Date], the price list has [Start Date] field and the [Net Purchase Price].
Each line of sales should be added [Net Purchase Price] from the lines of the price list with the highest date.

e.g. Max([Starting Date]) <=[Posting Date]

TIA
Igor

 

1 Solution

Accepted Solutions
sunny_talwar

Try this expression

Sum(Aggr(
If(Max(TOTAL <Description, [Document No_], Lot, No_, [Posting Date], Vendor> If([Starting Date] <= [Posting Date], [Starting Date])) = [Starting Date], [Net Purchase Price])
, Description, [Document No_], Lot, No_, [Posting Date], Vendor, [Starting Date]))

Capture.PNG

View solution in original post

4 Replies
agni_gold
Specialist III
Specialist III

can you please explain with some sample data and required output. 

thepiapus
Contributor II
Contributor II
Author

I must add field Net Purchase price to each row on certain date.

sunny_talwar

Try this expression

Sum(Aggr(
If(Max(TOTAL <Description, [Document No_], Lot, No_, [Posting Date], Vendor> If([Starting Date] <= [Posting Date], [Starting Date])) = [Starting Date], [Net Purchase Price])
, Description, [Document No_], Lot, No_, [Posting Date], Vendor, [Starting Date]))

Capture.PNG

thepiapus
Contributor II
Contributor II
Author

Thanks a lot, you made my day 🙂