Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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), '# ###,##%')
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), '# ###,##%')
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.
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`?
@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!!