Last prices before date

[pcservicios]

Hello, I need help for a sales report.

This report is an example to explain my problem.

I have some products that are sold and others that are bought. They are not the same product. For example when I buy beer, I buy a barrel, but when I sell beer, I sell a jug. They are different product codes. I have a table to relate both products.

The report gives me the sales between the 2 selection dates, and what I need to find the price at which a product was purchased before the selected dates.

In the example, in May I sold 7 beer jar, but the last barrel of beer I bought in March and I need to get that price. I managed to get the date of the last purchase, but I can't get the price. In the example the price should be 45. And if I choose february the price should be 40.

Any help will be appreciated

[MayilVahanan]

Hi @pcservicios 

Try like below

=Aggr(if (DatePurchases=Date(max(Total<CodSaleProduct>if(DatePurchases<=Date($(vDateStart), 'DD/MM/YYYY') ,DatePurchases))), PricePurchases), DatePurchases, CodSaleProduct)

[pcservicios]

 A lot of thanks Mayil.  the formula works well, but if I have 2 or more purchases of an item on the same day it no longer works. In this case I need average price.

I upload a new version with this case. 

Thanks for your help

[MayilVahanan]

Hi @pcservicios 

If you want avg the price, try like below

=Avg(Aggr(if (DatePurchases=Date(max(Total<CodSaleProduct>if(DatePurchases<=Date($(vDateStart), 'DD/MM/YYYY') ,DatePurchases))), PricePurchases), DatePurchases, CodSaleProduct))

[pcservicios]

Hi MayiVahanan.

Thank again, but the problem isn't calculate avg price. I think version 2 of the report was not uploaded. The problem is when there are 2 or more purchases on the same day and the same product.

In test v2.qvw I added to purchase table this data:

01/03/20,30,1,43

[MayilVahanan]

Hi @pcservicios 

=Aggr(if (DatePurchases=Date(max(Total<CodSaleProduct>if(DatePurchases<=Date($(vDateStart), 'DD/MM/YYYY') ,DatePurchases))), Sum(PricePurchases)), DatePurchases, CodSaleProduct)

Instead of sum(), based on ur requirement, you can change the exp

[pcservicios]

Hi again,  sorry for the inconvenience.

Have you tried the formula in the test v2.qvw report?

I understand that with sum it should give 88 and with avg it should give 44, but after chage the formula, but is not working like you can see on image.

A lot of thanks again

[MayilVahanan]

Same formula working fine

[pcservicios]

Are you working with test v2.qvw? this report have extra data with one purchase on same date. And my problem is there.

I think your example is with the first version of the report, sorry.

Please donwload test v2.qvw, and try with it, thanks.

[MayilVahanan]

Hi

Are you send the correct file?