8 Replies Latest reply: May 30, 2017 8:00 AM by Глеб Аитов

# y/y comparison of sales of current/previous collections (in Fashion)

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

YearCollectionCSCWCACRPSPWPAPR
2017SS1710000000
2017SS1600001000

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.

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

Hello Friend,

My solution is:

1. MasterCalendar

2. SetAnalysis.

To  the standard master calendar, you need to add :

1. MasterCalendarTemporary:

TempDate AS Date,

'Current' AS [PeriodType]

RESIDENT TempCal;

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.

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

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).

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

Could you upload a sample of data ?

Regards

Jacek.

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

sorry, not possible, it's huge and contains a lot of commercial information.

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

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

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)
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?

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

Hi Gleb,

May be data a structure like in the attached file?  As variant.

Regards,

Andrey

• ###### Re: y/y comparison of sales of current/previous collections (in Fashion)

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.