how to get new stores in new city

[Anonymous]

Hi Experts,

I have a data set from which I need to get the no of new stores in new city for a fiscal year..

below is the sample data set.

Store	City	Store open date	City Enter Date	fiscal year	sales
1	Delhi	4/26/2010	4/26/2010	2010-2011	100
10	Delhi	5/14/2011	4/26/2010	2011-2012	150
2	Mumbai	10/23/2011	10/23/2011	2011-2012	200
3	Mumbai	11/22/2011	10/23/2011	2011-2012	230
30	Mumbai	12/23/2012	10/23/2011	2012-2013	100
4	Chennai	10/15/2011	10/15/2011	2011-2012	123

and I want output for no of new stores in new city for a fiscal year

so the expected output would be:

Fiscal Year	# of new stores in new city
2010-2011	1
2011-2012	3
2012-2013	0

could any body please help me out to get it resolved.

Thanks in Advance.

Aashish

[Anonymous]

I think..

we need to get the no of stores where the store open date and city enter date lies in same fiscal year

[MayilVahanan]

Hi

Try like this

StoreTemp:
LOAD * INLINE [
    City, City Enter Date, fiscal year, sales, Store, Store open date
    Chennai, 10/15/2011, 2011-2012, 123, 4, 10/15/2011
    Delhi, 4/26/2010, 2010-2011, 100, 1, 4/26/2010
    Delhi, 4/26/2010, 2011-2012, 150, 1, 5/14/2011
    Mumbai, 10/23/2011, 2011-2012, 200, 2, 10/23/2011
    Mumbai, 10/23/2011, 2011-2012, 230, 3, 11/22/2011
    Mumbai, 10/23/2011, 2012-2013, 100, 3, 12/23/2012
];

Store:
Load If(Previous([City Enter Date]) = [City Enter Date] and City = Previous(City) and Previous([fiscal year]) <> [fiscal year], 0, 1) as Flag, *;
Load * Resident StoreTemp Order by City, [fiscal year];

DROP Table StoreTemp;

Dimension - [fiscal year]

Exp - Sum(Flag) gives the o/p

[PradeepReddy]

Please find the attachment.. it might be helpfull

[Anonymous]

hi Pradeep,

I am using personal edition of QlikView..

Could you please post me the script and expression.

Thanks

[MayilVahanan]

Hi

From the above script, you didn't achieve the result?

[PradeepReddy]

Test_Temp:

Load * inline

[

Store,City,Store_open_date, City_Enter_Date,fiscal_year,sales

1, Delhi, "4/26/2010", "4/26/2010", 2010-2011, 100

1, Delhi, "5/14/2011", "4/26/2010", 2011-2012, 150

2, Mumbai, "10/23/2011", "10/23/2011", 2011-2012, 200

3, Mumbai, "11/22/2011", "10/23/2011", 2011-2012, 230

3, Mumbai, "12/23/2012", "10/23/2011", 2012-2013, 100

4, Chennai,"10/15/2011", "10/15/2011", 2011-2012, 123

];

inner join

Load

City,fiscal_year,

date(Min(Store_open_date),'M/D/YYYY') AS Store_open_date_Min

resident Test_Temp

group by City,fiscal_year;

Temp:

load *,

IF(City_Enter_Date = date(Store_open_date_Min),'N','O') As 'Ind1'

Resident Test_Temp;

drop table Test_Temp;

Dimension : fiscal_year

Expression : Count({<Ind1='N'>}Store)

[sujeetsingh]

Sharma,

I think below answers meet your need.

[senpradip007]

Use this in script:

StoreTemp:

LOAD * INLINE [

Store, City, Store_Open_Date, City_Enter_Date, Fiscal_Year, Sales

1, Delhi, 4/26/2010, 4/26/2010, 2010-2011, 100

10, Delhi, 5/14/2011, 4/26/2010, 2011-2012, 150

2, Mumbai, 10/23/2011, 10/23/2011, 2011-2012, 200

3, Mumbai, 11/22/2011, 10/23/2011, 2011-2012, 230

30, Mumbai, 12/23/2012, 10/23/2011, 2012-2013, 100

4, Chennai,10/15/2011, 10/15/2011, 2011-2012, 123

];

inner join

Load

  City,

  Fiscal_Year,

  Date(Min(Store_Open_Date),'M/D/YYYY') AS Min_Store_Open_Date

Resident StoreTemp

group by City,Fiscal_Year;

Store:

NoConcatenate

LOAD

  *,

  if(City_Enter_Date = (Min_Store_Open_Date),'New','Old') As Flag

Resident StoreTemp;

DROP Table StoreTemp;

Create a straight table with Dimensions Fiscal_Year and count({ <Flag={'New'}>}Store) as Expressions. Also check "Suppress Null value" and "Show All values" in dimensions tab and unchecked "Suppress Zero Values" in Presentation tab.