Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
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 🙂