Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
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
Master II
Master II

@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
Specialist III
Specialist III

here is a simple suggestion with caveats:

edwin_0-1602902861523.png

 

the straight chart needs to be sorted by Product then date

Taoufiq_Zarra
Master II
Master II

@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

othonperrone
Contributor
Contributor
Author

That's it!! thank you @Taoufiq_Zarra