topic Re: Same Store analysis - Count and Sales in QlikView

Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 11:33:03 GMT

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

Re: Same Store analysis - Count and Sales

ali_hijazi — Wed, 12 Nov 2014 11:37:04 GMT

sum(aggr(count({< Store={"=count(Store)>1"}Store),Year)) --------------------> same store count

sum(aggr(sum({<store={"=count(Store)>1"}Sales),Year)) ---------------------->same store sales

Re: Same Store analysis - Count and Sales

alkesh_sharma — Wed, 12 Nov 2014 11:38:27 GMT

IF I may ask, Your data says in Year 2010 there is a store 123 then Y don't count up in your output table.

Re: Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 11:45:33 GMT

Hi Ali,

Your expression is not working.. it gives wrong result.. 😞

Re: Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 11:46:53 GMT

HI Alkesh,

assuming that the store 123 don't have sales in year 2009...

Re: Same Store analysis - Count and Sales

ali_hijazi — Wed, 12 Nov 2014 11:49:22 GMT

isn't it what you want is to find number of stores that have more than one trx per year?

Re: Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 11:54:04 GMT

Is this doable in QlikView ??

Re: Same Store analysis - Count and Sales

sujeetsingh — Wed, 12 Nov 2014 11:58:33 GMT

Straight table with Year as dimension

Count Distinct and sum for slaes

Re: Same Store analysis - Count and Sales

jyothish8807 — Wed, 12 Nov 2014 12:00:17 GMT

Hi Sharma,

I am not clear with your output table?

Can you explain a bit.

How you are getting 4 stores for 2013, then where store 124?

Regards

Re: Same Store analysis - Count and Sales

rubenmarin — Wed, 12 Nov 2014 12:02:29 GMT

Hi Sharma, check attachment, I precalculate values on script.

I think there is typo in the 2nd 128 store for 2013, it is a typo or store 128 has 2 records in 2013?

Re: Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 12:05:45 GMT

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

Re: Same Store analysis - Count and Sales

Anonymous — Wed, 12 Nov 2014 12:08:09 GMT

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.

Re: Same Store analysis - Count and Sales

rubenmarin — Wed, 12 Nov 2014 12:10:52 GMT

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;

Re: Same Store analysis - Count and Sales

sushil353 — Thu, 13 Nov 2014 05:02:13 GMT

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