Compare between two time periods

[Tmerinol]

Hi all,

I have an historical database where I have the orders for different products for the company (sold + forecast) for the year 2021. I have the historical data since 01.01.2021 for every day until now and i want to compare the percentual change between the amounts (sold + forecast) in different periods of time but limitating this just to two periods (otherwise it would not possible to compare three periods)

For example:  compare the percentual difference for all the articles between the 04.09.2021 and 01.09.2021, or between today and yesterday or between two random dates (that the user can choose).

I would like to know if it is possible to do this with a Pivot Table in Qlikview

Dataset would be: 

Amount of orders and sold articles for diferents products for the year 2021
Artikel	01.09.2021	02.09.2021	03.09.2021	04.09.2021	…
A	10	15	20	20	…
B	20	25	40	45	…
C	30	30	50	40	…

Expected Result would be (for the selected dates): 

Artikel	03.09.2021	04.09.2021	Difference between selected datum 1 (04.09.2021) and datum 2 (03.09.2021)
A	100	40	40,00%
B	40	22	55,00%
C	50	40	80,00%

Thanks a lot in advance!

[MayilVahanan · Accepted solution]

Hi @Tmerinol 

Hope the user will select max 2 random dates, in that case, try like below

Dim: Artikel

Exp1: Sum({<DateField={'$(=Date(Min(DateField)))'}>}Sales)

Exp2: Sum({<DateField={'$(=Date(Max(DateField)))'}>}Sales)

Exp3: Num(Sum({<DateField={'$(=Date(Max(DateField)))'}>}Sales) / Sum({<DateField={'$(=Date(Min(DateField)))'}>}Sales), '# ###,##%')

[Vegar]

it is possible to do, but I would recommend you to normalise your data set in order to do do. 

Try to transform your data into this format.

|Artikel|Date|Amount|

|A|01.09.2001|10|

|A|02.09.2001|15|

|B|01.09.2001|20|

|B|02.09.2001|25|

You could try to achieve this by using the CROSSTABLE functionality in your load script.

When you have done this,  then you could look at @MayilVahanan 's reply in order to get a correct measure expression. 

[Tmerinol]

Hi ,

Thanks for the reply. I have already the data normalised. 

I tried the formulas you said but it is not working. I dont know why.

In this case i have the dimension "Artikel". the variable "von" means "Date" and absatz are the "Sales". 

I wrote the three expressions but they seems to not work. 

What should i do`?

[Tmerinol]

@MayilVahanan  your solution worked for me. I realized that i had a format problem (with the dates). I fixed that and now works!

Thank you very much!!