8 Replies Latest reply: Nov 1, 2015 8:56 PM by Jim Chan

# New store and existing store comparison

Hi guys,

i need to create something that it is able to let me know what's my new store sales , what's my existing store sales.

i have a table as such:

store nameSalesYear
store A1m2015
store B2m2015
store A1m2014
store B02014

from the table of cos i know store b is new store as there's no sales in 2014, but 2015. but in Qlikview, how can I achieve something as below:

Assuming i have set to YTD- 2015

All Store:3m

New Store: 1m

• ###### Re: New store and existing store comparison

Hi jim

From this data:

Inline [

store name,Sales,Year

store A,1000000,2015

store B,2000000,2015

store A,1000000,2014

];

This expression returns 2.000.000 (Sales of store B):

=Sum({<[store name]=E({<Year={'\$(=Max(Year)-1)'}>} [store name])>} Sales)

• ###### Re: New store and existing store comparison

something New! what is the E for?

• ###### Re: New store and existing store comparison

What if my data is

Inline [

store name,Sales,Year

store A,3000000,2015

store B,1000000,2015

store A,3000000,2014

store B, 0, 2014

];

can i still get to know store b is my new store because there's no sales in 2014. and just started in 2015.

• ###### Re: New store and existing store comparison

Hi jim, the E() is to filter excluded values, for the case when store doesn't have any record in past year (not even a 0 sales record), wich is very common when creating new stores.

If you have a record with 0 sales this expression can work with nulls and '0' sales:

=Sum({<[store name]=E({<Year={'\$(=Max(Year)-1)'}>} [store name])>+<[store name]={"=Sum({<Year={'\$(=Max(Year)-1)'}>} Sales)=0"}>} Sales)

This way you get <the stores excluded when you select the past year> plus <the stores which have a record but the sum of sales is 0>.

• ###### Re: New store and existing store comparison

Btw, if you want to ensure that you're comparing one year with another you can filter to sum only sales of the max year:

=Sum({<[store name]=E({<Year={'\$(=Max(Year)-1)'}, Year={'\$(=Max(Year))'}>} [store name])>+<[store name]={"=Sum({<Year={'\$(=Max(Year)-1)'}>} Sales)=0"}, Year={'\$(=Max(Year))'}>} Sales)

This way if you have data from year 2010 to 2015 and the user doesn't selects any year, the expression returns the sales of the stores with sales in 2015 but without sales in 2014.

If user selects 2013 the expression returns the sales of the stores with sales in 2013 but not in 2012.

• ###### Re: New store and existing store comparison

Thanks. a lot of readings. thanks ruben,i will test on it.

• ###### Re: New store and existing store comparison

see the attachment, it might be helpful..

• ###### Re: New store and existing store comparison

Hello pradeep ! thanks for your help! i will try it out soon! thanks!