Same Store analysis - Count and Sales

[Anonymous]

Hi All,

I have a data set on which I need to do Same Store analysis..

Same Store are the stores having sales last year and this year..

below is the sample data

Year	Store Id	Sales
2013	123	100
2013	124	200
2013	125	300
2013	126	400
2013	127	500
2013	128	600
2013	129	700
2012	123	800
2012	125	900
2012	127	1000
2012	128	200
2011	124	300
2011	123	500
2011	129	580
2011	125	900
2010	123	1000

so based on the above data set I need same store analysis as below:

Year	Same Store Count	Same Store Sales
2013	4	1500
2012	2	1700
2011	1	500
2010	0	0

The above output is based on the stores have sales to there previous year

for 2013 -- store count =4 (123,127,125,128) and similarly sales =1500 (100+300+500+600)

for 2012 -- store count =2 (123,125) and sales = 1700 (800+900)

how can I achieve this in a straight table..

Thanks in advance.

Aashish

[Anonymous]

Hi KC,

I want to calculate the count of those store which are active last year..

for example in stores in 2013 are (123,124,125,126,127,128)  but out of these store only 4 were active or having sales in last year so in year 2012 (123,125,127,128) that is why in 2013 the count is 4..

hope I am able to clear your doubt..

[Anonymous]

hi Ruben,

Yes it was Typo.. I corrected it to 129..

I can't open your qvw since I am using personal edition.. could you post your solution here.

[rubenmarin]

Ok, expressions are simple: sum(SameStore) and sum(SameSales)

This is my script:

Initial:

LOAD * Inline [

Year,Store Id,Sales

2013,123,100

2013,124,200

2013,125,300

2013,126,400

2013,127,500

2013,128,600

2013,129,700

2012,123,800

2012,125,900

2012,127,1000

2012,128,200

2011,124,300

2011,123,500

2011,129,580

2011,125,900

2010,123,1000

];

Data:

NoConcatenate

LOAD Year,

    [Store Id],

    Sales,

    If(Previous([Store Id])=[Store Id] and Previous(Year)=Year-1, 1) as SameStore,

    If(Previous([Store Id])=[Store Id] and Previous(Year)=Year-1, Sales) as SameSales

Resident Initial

Order by [Store Id], Year;

DROP Table Initial;

[sushil353 · Accepted solution]

Hi Sharma,

Try below code:

Temp:

load Year&Store as Key,

Year,

Store,

Sales

from source_file;

left join

Load (Year+1)&Store as Key,

1 as SSFlag

Resident Temp;

now in the expression you can simply use this flag to count and get the sales.

count({<SSFlag={1}>}Store)

sum({<SSFlag={1}>}Sales)

HTH

sushil