Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think..
we need to get the no of stores where the store open date and city enter date lies in same fiscal year
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
Please find the attachment.. it might be helpfull
hi Pradeep,
I am using personal edition of QlikView..
Could you please post me the script and expression.
Thanks
Hi
From the above script, you didn't achieve the result?
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)
Sharma,
I think below answers meet your need.
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.