Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello, I am trying to recreate the following example in QlikSense.
Here is my case:
I have data records from the autumn / winter 2020 (FW20) season and would like to calculate a sales forecast and a reorder quantity for FW 21 for the coming autumn / winter 2021 (FW21) season.
First, I had all the data for FW20 displayed in a table, as well as for FW21 with the data records that already exist.
Now I'm trying to create a factor for FW21 in order to be able to calculate the sales forecast with the factor.
It worked in Excel and looks like this:
(The current date is 10/01/2021 in the test data set)
The last three color-coded cells are still missing in Qlik Sense.
Formulas in Excel are:
Factor vs last year = Sales per Day (FW21)/ average sales per ID and per day (FW20)
Sales forecast current year with reference last year = Factor vs last year (FW21)* average sales per ID (FW20) * Return Rate (FW20)
Reorder quantity fort he current year with reference from last year = Sales forecast current year with reference last year (FW21) – Total Sales (FW21) – available Stock (FW21) + Total Returns (FW21)
FW21:
FW20:
My formula in Qlik Sense for the metric "Factor vs last year" looks like this:
IF([Matching:Brand,Category, Size] = if([Matching:Brand,Category,Size]= [Matching:Brand,Category,Size_Reference], [Matching:Brand,Category,Size_Reference], '-'),
(Sum({<[date]={">=2021-06-01<=2022-03-31"}, [type] = {"sale"}, [season]={"HW21"}>}amount)
/
(Date(Today())
-
Min({<[type]={"sale"}, [season]={"HW21"}, [date]={">=2021-06-01<=2022-03-31"}>}[date])))
/
(Sum({<[type]={"sale"}, [date] = {"$(='>=' & 2020-06-01 & '<=' & Date(today()-365))"}, [season]={"HW20"}>}[amount])/
count({<[type]={"sale"}, [date] = {"$(='>=' & 2020-06-01 & '<=' & Date(today()-365))"}, [season]={"HW20"}>}[ID]))
/
(Date(Today()-365) - IF((season = 'HW20'),min({$<[date] = {"$(='>=' & 2020-06-01 & '<=' & Date(today()-365))"}, [season]={"HW20"}>}date))))
With the IF statement, I check whether there is an equal matching of brand, category and size in the FW20 season to the FW21 season. If there is, then the formula should be used. The formula shows me no syntax error, but no values are output, although there should be. I think the error is due to the verification of the ID in FW20 which does not exist in FW21 (count ({<[type] = {"sale"}, [date] = {"$ (= '> =' & 2020- 06-01 & '<=' & Date (today () - 365)) "}, [season] = {" HW20 "}>} [ID]))).
It would be great, if somebody could help me.
The picture are also in the appendix.
