Populate change in price data between 2 dates

[Rich5678]

Hi,

I have the following database with Monthly Price data for past 2 years.
(sample extract of data is shown below).

Date	Fruit	Price
6/30/2023	Orange	1.5
6/30/2023	Apple	1.25
6/30/2023	Mango	2
6/30/2023	Banana	0.5
5/31/2023	Orange	1.2
5/31/2023	Apple	0.9
5/31/2023	Mango	1.5
5/31/2023	Banana	0.4
4/30/2023	Orange	1.75
4/30/2023	Apple	1.98
4/30/2023	Mango	2.25
4/30/2023	Banana	0.38

I would like to build a Qlik Sense App with 2 Filter Panes on top (which look at "Date" column in above table).

First Filter pane will ask the "Current Date" and second Filter pane will ask for "Previous Date".

Could you please help, as to how to setup the Current Date and Previous Date filter panes. 
If I just drag the "Date" column to filter panes, it is NOT working out, because both Current Date and Previous Date are changing at the same time. 

These 2 date selections are to be different - could you please help how to accomplish that.

After the User selects "Current Date" and "Previous Date", following table needs to be populated.

	Current Date 6/30/2023	Previous Date 4/30/2023	Change
Orange	1.5	1.75	-0.25
Apple	1.25	1.98	-0.73
Mango	2	2.25	-0.25
Banana	0.5	0.38	0.12

Could you please provide guidance, on how the result of selection in above Filter Panes, can become the input of Columns 2 and 3 in above Table.

[cristianj23a · Accepted solution]

Hi, try this code.

Fruits:
Load * Inline [
Date,Fruit,Price
6/30/2023,Orange,1.5
6/30/2023,Apple,1.25
6/30/2023,Mango,2
6/30/2023,Banana,0.5
5/31/2023,Orange,1.2
5/31/2023,Apple,0.9
5/31/2023,Mango,1.5
5/31/2023,Banana,0.4
4/30/2023,Orange,1.75
4/30/2023,Apple,1.98
4/30/2023,Mango,2.25
4/30/2023,Banana,0.38
];

TempTable:
LOAD *,
If(Fruit=Peek(Fruit), Peek(Price)) as Price_current,
If(Fruit=Peek(Fruit), Peek(Date)) as Date_current
Resident Fruits
Order By Fruit, Date Desc;

DROP Table Fruits;

Fruits:
LOAD *,
If(Fruit=Peek(Fruit), Price) as Price_former,
If(Fruit=Peek(Fruit), Date) as Date_former
Resident TempTable
Order By Fruit, Date Desc;

DROP Table TempTable;