topic Re: Create Column with previous sales value for same dimension in QlikView

Create Column with previous sales value for same dimension

othonperrone — Fri, 16 Oct 2020 23:17:16 GMT

Hi Guys, I am new to Qlik sense and I need a help to solve this issue:

I need to retrieve previous data from same dimension ,

For example, for order "1" I need to check what was the previous sales date for product A before 03/06/2020 (dd/mm/yy format) and bring the value for that order, in this example it was order 7 that occurred in 14/01 with a R$43 value.
I need this previous value for each line.

Table1:

Order	Product	Value	Date	Previous Value for Product
1	A	R$30,00	03/06/2020	R$43,00
2	B	R$40,00	20/06/2020	-
3	B	R$50,00	13/09/2020	R$45,00
4	C	R$60,00	09/05/2020	-
5	C	R$65,00	06/09/2020	R$60,00
6	C	R$35,00	01/06/2020	R$60,00
7	A	R$43,00	14/01/2020	-
8	B	R$23,00	15/12/2020	R$50,00
9	B	R$45,00	25/06/2020	R$40,00

Thankyou in advance

Re: Create Column with previous sales value for same dimension

edwin — Sat, 17 Oct 2020 02:48:32 GMT

here is a simple suggestion with caveats:

the straight chart needs to be sorted by Product then date

Re: Create Column with previous sales value for same dimension

Taoufiq_Zarra — Sat, 17 Oct 2020 08:47:56 GMT

@othonperrone in case you want to do it at script level :

Data: LOAD * INLINE [ Order, Product, Value, Date 1, A,30, 03/06/2020 2, B,40, 20/06/2020 3, B,50, 13/09/2020 4, C,60, 09/05/2020 5, C,65, 06/09/2020 6, C,35, 01/06/2020 7, A,43, 14/01/2020 8, B,23, 15/12/2020 9, B,45, 25/06/2020 ]; Output: noconcatenate Load *,if(peek(Product)=Product,peek(Value)) as [Previous Value for Product]; load * resident Data Order By Product ASC, Date ASC ; drop table Data;

output:

I think befor order 5 its 6!

Re: Create Column with previous sales value for same dimension

othonperrone — Sun, 18 Oct 2020 22:30:15 GMT

That's it!! thank you @Taoufiq_Zarra