topic Re: Last prices before date in QlikView

Last prices before date

pcservicios — Mon, 19 Apr 2021 12:07:36 GMT

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

Re: Last prices before date

MayilVahanan — Tue, 20 Apr 2021 01:07:39 GMT

Hi @pcservicios

Try like below

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

Re: Last prices before date

pcservicios — Tue, 20 Apr 2021 10:52:24 GMT

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

Re: Last prices before date

MayilVahanan — Wed, 21 Apr 2021 01:14:44 GMT

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))

Re: Last prices before date

pcservicios — Wed, 21 Apr 2021 07:52:55 GMT

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

Re: Last prices before date

MayilVahanan — Wed, 21 Apr 2021 08:33:05 GMT

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

Re: Last prices before date

pcservicios — Wed, 21 Apr 2021 08:54:17 GMT

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

Re: Last prices before date

MayilVahanan — Wed, 21 Apr 2021 09:07:42 GMT

Same formula working fine

Re: Last prices before date

pcservicios — Wed, 21 Apr 2021 09:16:12 GMT

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.

Re: Last prices before date

MayilVahanan — Wed, 21 Apr 2021 10:08:59 GMT

Are you send the correct file?

Re: Last prices before date

pcservicios — Thu, 22 Apr 2021 08:24:27 GMT

Hi Mayil, I don't know what happend.

I have downloaded your file and everything works fine. But in mine I copy the same formula and it doesn't work. I do not get it.
I have also tried to copy the table from one report to another and the data does not come out :-(.

It is very rare. I'm going to make up what's going on. Thank you very much for your help