Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
Not applicable

Like for Like Sales Calculation

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.

7 Replies

Re: Like for Like Sales Calculation

See attched qvw. Is that what you need?

talk is cheap, supply exceeds demand
Not applicable

Re: Like for Like Sales Calculation

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.

Contributor II

Re: Like for Like Sales Calculation

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.


New Contributor III

Re: Like for Like Sales Calculation

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.

New Contributor III

Re: Like for Like Sales Calculation

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.

Not applicable

Re: Like for Like Sales Calculation

Hi Vadim,

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



New Contributor III

Re: Like for Like Sales Calculation

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,


Community Browser