Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I am in fashion and I am trying to build an app with analysis of performance of different COLLECTIONS year to year.
Currently I have sales split by dimension called COLLECTION.
It look like the following:
SS17 means SummerSpring 2017
FW17-18 means FallWinter 2017-2018
ALL means carryover apparel (does not belong to any season)
In order to analyze sales, I have to compare the collections in pairs. For example, SS17 sales for 2017 is compared to SS16 in 2016.
What i need:
Once YEAR is chosen, the chart shows the sales of the current collection for the chosen year and compares it to sales of the previous collection one year ago. For example, when 2017 is chosen, the "SS performance chart" should show the SS17 sales in 2017 vs SS16 sales in 2016.
I am able to compare year to year using {PY calendar}, but i can' figure it out how to tell Qlik to show SS16 in Previous Year, and not SS17.
I feel the solution is quite simple. One possible way is to:
1. Create flags for each collection, for each year:
CS - current summer
CW - current winter
CA - current allseason
CR - current other (not CS,CW and CA, basically, all other previous seasons sold in current year)
from PS to PR - same as above, but previous year collections
Year | Collection | CS | CW | CA | CR | PS | PW | PA | PR |
---|---|---|---|---|---|---|---|---|---|
2017 | SS17 | 1 | 0 | 0 | 0 | 0 | 0 | 0 | 0 |
2017 | SS16 | 0 | 0 | 0 | 0 | 1 | 0 | 0 | 0 |
2. Place two measures on a graph:
Measure1: SUM ( {< CS=1 >} Sales)
Measure2: SUM ( {< PS=1 >} Sales)
(in this example, summer collection sales are compared)
My questions:
1. Is there a less complicated solution to this?
2. If you feel my solution is ok, can anybody help me with the load script, so for each possible combination of year and collection, the above table is created? (unfortunately I am not a programmer)
Many thanks!
Gleb.
Hello Friend,
My solution is:
1. MasterCalendar
2. SetAnalysis.
To the standard master calendar, you need to add :
1. MasterCalendarTemporary:
LOAD TempDate AS %keyDate,
TempDate AS Date,
'Current' AS [PeriodType]
RESIDENT TempCal;
LOAD ADDYEARS(TempDate, -1) AS %keyDate,
TempDate AS Date,
'PrevYear' AS [PeriodType]
RESIDENT TempCal;
2. In set analysis :
sum(<[~PeriodType]={Current}>}sales) as sales from 2017
sum(<[~PeriodType]={PrevYear}>}sales) as sales from 2016
Regards
Jacek.
Hi Jacek,
I've done this already (please, see my post). My Master Calendar is set.
The problem is different - I need a sort of Master Calendar for COLLECTION dimension (not the DATE dimension).
Could you upload a sample of data ?
Regards
Jacek.
sorry, not possible, it's huge and contains a lot of commercial information.
Hi,
You like to show 2017 sales and 2016 sales side by side in same table?
Or you trying to get difference between 2017 and 2016 sales? Only 2 yr comparison or between 2017, 2016, 2015 and so on...?
As Jacek suggest in front end you need to use Set expression and in script you can use flags for CY, PY or you can use Rolling months as well
You like to show 2017 sales and 2016 sales side by side in same table?
Yes, but on the same chart. I am able to do so.
measure1: sum (<Calendar={CP}> sales )
measure2: sum (<Calendar={PY}> sales )
No problem with it. Until I add a COLLECTION dimension.
That's what i need:
Year Filter: 2017
measure1: sum (<Calendar={CP}, Collection={'SS17'}> sales )
measure2: sum (<Calendar={PY}, Collection={'SS16'}> sales )
It works fine when my year is 2017. When i choose 2016, i want Qlik to change measures automatically as follows:
Year Filter: 2016
measure1: sum (<Calendar={CP}, Collection={'SS16'}> sales )
measure2: sum (<Calendar={PY}, Collection={'SS15'}> sales )
And so on. Dynamically.
I came up with a solution (see my original post). My question is - maybe there is another, more simple solution?
Hi Gleb,
May be data a structure like in the attached file? As variant.
Regards,
Andrey
Hi Andrey,
Thank you for the solution. Unfortunately it does not suit by monster data structure from 1C.
I actually applied my solution with flags, therefore, the discussion is closed.
Thank you,
Gleb.