Finding values based on date

[#1]

Hi,

I have a data in which there are several purchase orders (POs) with item numbers, quantity and expected receipt date.

Now i need to find out per item number first three expected receipt dates which should be greater than today i.e. should be in future. Besides that I also need to find out how much quantity will be received for that item number on these dates.

I have calculated the first three expected receipt dates in future per item but the quantity for these dates is giving me some headache.

I have attached herewith the excel file containing source data plus the Qlikview file in which I have calculated the first three dates.

I have used below formulas for calculating dates

1st date:

=min({<[Expected Receipt date]= {'>=$(=today())'}>} date(([Expected Receipt date])))

2nd date:

=min({<[Expected Receipt date]= {'>=$(=today())'}>} date(([Expected Receipt date])),2)

3rd date:

=min({<[Expected Receipt date]= {'>=$(=today())'}>} date(([Expected Receipt date])),3)

The formula which I am using to calculate quanity for above dates is as below:

Quantity for 1st date:

=sum(Aggr(if([Expected Receipt date]= min({<[Expected Receipt date]= {'>=$(=today())'}>} (([Expected Receipt date]))),[Quantity]),%Key))

I expect below outcome:

Item Number	Date 1	Quantity 1	Date 2	Quantity 2	Date 3	Quantity 3
C002-C01-08-WLM-WLM-09A	02/10/2017	20	02/11/2017	35	02/12/2017	45
C002-C02-08-WLM-WLM-09A	08/10/2017	22	08/11/2017	25	08/12/2017	35
C002-C03-08-WLM-WLM-09A	02/11/2017	25	02/12/2017	36	10/12/2017	63
C002-C04-08-WLM-WLM-09A	09/11/2017	20	09/12/2017	24	18/12/2017	42

Request you to help me with the calculation.

Thank you for your all support,

Ashish Malkar

[Kushal_Chawda · Accepted solution]

try below expression for Quantity 1

=sum(aggr(if([Expected Receipt date]=min(total <[Item Number]>{<[Expected Receipt date]= {'>=$(=today())'}>} (([Expected Receipt date]))),[Quantity]),[Expected Receipt date],[Item Number],[Line No.]))

like wise you can create the same expression for Quantity 2,3 4

[Kushal_Chawda]

is this the same thread?

Finding Values based on date

[#4]

Thank you very much Kushal for the calculation. It is working as required.

Kind regards,

Ashish Malkar

[#5]

Yes, its same thread posted by my colleague.