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: 
othonperrone
Contributor
Contributor

Create Column with previous sales value for same dimension

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:

OrderProductValueDatePrevious Value for Product
1AR$30,0003/06/2020R$43,00
2BR$40,0020/06/2020-
3BR$50,0013/09/2020R$45,00
4CR$60,0009/05/2020-
5CR$65,0006/09/2020R$60,00
6CR$35,0001/06/2020R$60,00
7AR$43,0014/01/2020-
8BR$23,0015/12/2020R$50,00
9BR$45,0025/06/2020R$40,00


Thankyou in advance

1 Solution

Accepted Solutions
Taoufiq_Zarra

@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:

Capture.PNG

I think befor order 5 its 6!

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉

View solution in original post

3 Replies
edwin
Master II
Master II

here is a simple suggestion with caveats:

edwin_0-1602902861523.png

 

the straight chart needs to be sorted by Product then date

Taoufiq_Zarra

@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:

Capture.PNG

I think befor order 5 its 6!

Regards,
Taoufiq ZARRA

"Please LIKE posts and "Accept as Solution" if the provided solution is helpful "

(you can mark up to 3 "solutions") 😉
othonperrone
Contributor
Contributor
Author

That's it!! thank you @Taoufiq_Zarra