Get values for 2 dates in 2 columns

[Rich5678]

Simple question please..
I have following data points

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

I just want to get a simple table like below (Not using Pivot tables).

I will have 2 Filter Panes on top, so that the user can pick "Current Date" and "Previous date".

Assume that Current Date is chosen as 6/30/2023 and Previous Date is chosen as 5/31/23.

And after above user selections, I would like to get below output, as a simple Table

	6/30/2023	5/31/2023	Change
Orange	1.5	1.2	0.3
Apple	1.25	0.9	0.35
Mango	2	1.5	0.5
Banana	0.5	0.4	0.1

For your convenience, below is the inline loader script:

Load * inline [
Date,Fruit,Price
06/30/2023,Orange,1.5
06/30/2023,Apple,1.25
06/30/2023,Mango,2
06/30/2023,Banana,0.5
05/31/2023,Orange,1.2
05/31/2023,Apple,0.9
05/31/2023,Mango,1.5
05/31/2023,Banana,0.4
];

[joaopaulo_delco · Accepted solution]

Hi @Rich5678 !

          You can try this:

// Column 1
Dimension = Fruit

// Column 2
//Expression: 
Avg( {<Date={"$(=Pick(2, $(=Concat(distinct Chr(39) & Date & Chr(39), ', '))))"}>}  Price)
//Label: 
=Pick(2, $(=Concat(distinct Chr(39) & Date & Chr(39), ', ')))

//Column 3
//Expression: 
Avg( {<Date={"$(=Pick(1, $(=Concat(distinct Chr(39) & Date & Chr(39), ', '))))"}>}  Price)
//Label: 
=Pick(1, $(=Concat(distinct Chr(39) & Date & Chr(39), ', ')))

// Column 4
// Expression:
Column(1)-Column(2)

[Rich5678]

Many thanks, but could you clarify what "$(Pick(1, $(=Concat..... " is doing?
What is 1 here

Thanks

[Rich5678]

Could you please clarify, how to assign Current Date value to the selection from "Current Date" filter pane.

And how to assign that, within the Expression

[joaopaulo_delco · Accepted solution]

@Rich5678  , you can add new column with the separated expressions to see the results, but I'll try to explain.

The below expression returns a list of all distinct possible dates.

=Concat(distinct Chr(39) & Date & Chr(39), ', ')

The result will be something like that

The pick function returns the data in a position, so when I set the first parameter like "1",  it will return the first position of a list of data. 05/31/2023 in that case. If your set the parameter as "2", it will return the second register. 06/30/2023 in that case.

So I combine the two techniques to solve the problem.

I suggest you read the Pick function documentation.

https://help.qlik.com/en-US/sense/May2023/Subsystems/Hub/Content/Sense_Hub/Scripting/ConditionalFunc...

[Rich5678]

Thank you so much,

I sincerely appreciate your elaborate guidance.

However, I made my first question, unrealistically simple, and definitely, not as a Practical case.

Therefore, I have modified after posting original query (within about 15 minutes of original post).

In a User facing scenario, I will have 2 Filter Panes on top.

First Filter pane will ask for Current date, and second filter pane  will ask for Previous date (from the User).

Based on the selections in these 2 filter panes, above table need to be built.

Can you please look at my question again.

[joaopaulo_delco · Accepted solution]

@Rich5678 

What kind of field are you using in the filter panel? A Variable, a field from a table?

It makes a difference.

If your are using a field from a table, you can try something like this:

// For the Current Date
Avg( {<Date={"=$(Only(CurrentDate))"}>}  Price)
// For the previous Date
Avg( {<Date={"=$(Only(PreviousDate))"}>}  Price)