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]

Any idea how to get this??

[sujeetsingh]

You can get this through FirstSorted value function in the onject with aggrg function.

Folow the link

WHat is the FirstSortedValue()

[sujeetsingh]

One more thing is that how you are going to calculate that which opened one is latest or new.

[Anonymous]

Hi Surjeet,

Thanks for your reply,

based on the first open date is the criterion to get the fiscal year of opening a city or store...

Here In above example for fiscal year 2012-2013 we should get the zero value because Mumbai city has opened in 2011-2012...

also, I am not able to get.. how we can use firstsortedvalue() function to count the no new stores in new city..

could you please give me some idea to write the expression

[sujeetsingh]

Sharma,

I am confused cam you please explain that how are you calculating the new stores here.

[ashwanin]

Hi Sharma,

I think you have to do the below,

1: you have to create a new field(year e.g named it Enteryear) from City Enter Date .

2: From Fiscal year field you have to took Fiscalfirstyear.

3: Then you have to calculate the count of stores with condition : if (Enteryear > Fiscalfirstyear  (count (Stores))

[MayilVahanan]

HI

For 2010-2011, Delhi - 4/26/2010

For 2011-2012, Mumbai and Chennai only know.

Could you please explain

For 2011-2012, how you are saying 3 as new stores?

[Anonymous]

Hi Surjeet,

Based on the store open date we can find out the new store opened in a fiscal year.

But I need to calculate the no of store based on the condition...

that is >>.  the new store should be from new city...

the new city we can get from city enter date for a fiscal year..

[Anonymous]

hi Mayil,

for 2011-2012

one store is from chennai

and two store (2,3) are from Mumbai.. since these two are opened in 2011-2012