topic Re: Like for Like Sales Calculation in QlikView

Like for Like Sales Calculation

Thu, 25 Oct 2012 09:22:21 GMT

Attached there is a qlikview file gathering data from Excel file for Sales per Store. As a retail company It is significant to Compare Sales per store and Company Totals on Daily basis with the exact day last year. In my example there are three stores: Store A, B and C. Store A opened doors on 01/10/2011 and steel open till today. Store B opened doors on 04/10/2011 and still open and Store C is opened on 01/10/2011 and closed on 06/10/2011. The target is to select one date in 2012 and the table return the sales for that day per store, the sales for that day last year and Company totals for Day to Day, month to month and year to year comparison only for operational dates.

Thanks in advance.

Re: Like for Like Sales Calculation

Gysbert_Wassenaar — Thu, 25 Oct 2012 11:28:25 GMT

See attched qvw. Is that what you need?

Re: Like for Like Sales Calculation

Fri, 26 Oct 2012 07:33:36 GMT

I have to say that is a very interesting aproach but the target is to calculate sales, let's say for the B store this year sales have to be calculated from the correspending date of the openening day (04/10/2012). And for the C Store respectively last year month sales have to be calculated till the last day working of this year. (02/10/2011).

Thank you anyway.

Note: I have update the data file. and used with your qlikview file.

Re: Like for Like Sales Calculation

Anonymous — Sat, 27 Oct 2012 14:15:49 GMT

I would create a like for-like-dimension table for each combination of store and date with a flag weather this combination is Like-for-like acceptable.

I would also create a calendar dimension table with a corresponding like for like date for each date in the calendar.

Then you should then be able with some set analysis to make your calculation on all measures in your fact table.

/Henrik

Re: Like for Like Sales Calculation

Anonymous — Tue, 15 Oct 2013 14:50:01 GMT

Like for Like Sales

It is common to require to do a "like-for-like" comparison in the retail sector. An approach is to use "flags" in the script and to use these flags to calcuate the comparable sales amounts for the current & prior periods. The script calcualtes flags, at a record level for:

a) "comparable" sales in Current Period

b) "comparable" sales in Prior Period

The basis of comparison usually requires to be considered carefully. In a simple example (as in your data set) the key is that the Store is open on the same Date in the previous year; this provides (a) the comparable sales in the current period. However, it is also necessary to calculate (b) the "comparable" sales in the prior period to ensure that the comparison reflects any closures in the current period.

This is a generic approach, and I hope it helps in your case.

To achieve your analysis requirements you will require to use Set Analysis to enable the selection of a Date and the calcualtion of the Month & Year values. I have included some basic set analysis.

Re: Like for Like Sales Calculation

vadim_grab — Sun, 08 Jan 2017 12:24:33 GMT

Nicolas, hello.

Can you place script text your L4L_FlagMethod. I'm working on L4L Sales Compare Method for FMCG Distributor. I need compare Year-To-Year Volume, only for Customer which was in LYTD and now YTD.

Thanks a lot.

Re: Like for Like Sales Calculation

Thu, 10 Aug 2017 18:44:28 GMT

Hi Vadim,

Were you able to derive the L4L_Flag? If yes can you please share the script please.

Thanks,

Ravi

Re: Like for Like Sales Calculation

vadim_grab — Mon, 18 Sep 2017 19:04:12 GMT

Hi Ravi!

Sorry for long answer.

Unfortunately, Nicolas never answered! I'm park this question for the best time!

If you solved the issue already, pls, share you solution.

Best regards,

Vadim