Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
ziabobaz
Creator III
Creator III

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.

8 Replies
Anonymous
Not applicable

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.

ziabobaz
Creator III
Creator III
Author

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

Anonymous
Not applicable

Could you upload a sample of data ?

Regards

Jacek.

ziabobaz
Creator III
Creator III
Author

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

MK9885
Master II
Master II

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

ziabobaz
Creator III
Creator III
Author

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?


ahaahaaha
Partner - Master
Partner - Master

Hi Gleb,

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

Regards,

Andrey

ziabobaz
Creator III
Creator III
Author

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.