topic Re: Compare between two time periods in QlikView

Compare between two time periods

Tmerinol — Wed, 08 Sep 2021 13:34:05 GMT

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!

Re: Compare between two time periods

MayilVahanan — Thu, 09 Sep 2021 03:20:30 GMT

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), '# ###,##%')

Re: Compare between two time periods

Vegar — Thu, 09 Sep 2021 04:28:07 GMT

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.

Re: Compare between two time periods

Tmerinol — Thu, 09 Sep 2021 14:14:52 GMT

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`?

Re: Compare between two time periods

Tmerinol — Thu, 09 Sep 2021 15:27:18 GMT

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