Solved: Same Store analysis - Count and Sales - Qlik Community

Anonymous · ‎2014-11-12

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

sushil353 · ‎2014-11-13

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

View solution in original post

ali_hijazi · ‎2014-11-12

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

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

I can walk on water when it freezes

alkesh_sharma · ‎2014-11-12

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.

Anonymous · ‎2014-11-12

Hi Ali,

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

Anonymous · ‎2014-11-12

HI Alkesh,

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

ali_hijazi · ‎2014-11-12

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

I can walk on water when it freezes

Anonymous · ‎2014-11-12

Is this doable in QlikView ??

sujeetsingh · ‎2014-11-12

Straight table with Year as dimension

Count Distinct and sum for slaes

jyothish8807 · ‎2014-11-12

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

KC

Best Regards,
KC

rubenmarin · ‎2014-11-12

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?