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