6 Replies Latest reply on Jul 17, 2018 2:45 AM by Saharat Sakulsaowapakkul

# Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

Hi Every,

I have datasource with store name also its sales in my Tableau workbook.

I am creating new dimension call "Is Newly Opened."

Its logic calculation (In verbal) would be as following.

IF [Sales] in last 3 or earlier is NULL (or 0)

THEN  "Newly Opened"

ELSE "Existing"

Below is an example screenshot of my workbook.

The logic calculated field "Is Newly Opened" would consider CMR store as "Existing"

but consider CNK store as "Newly Opened" since its sales in 2015 is completely NULL

Hope fully someone could revise it into correct formula fit to Tableau accordingly.

Identify new store.twb

• ###### 1. Re: Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

Hi Saharat,

1. create max year =  {fixed:max(year([Order Date]))}.

2. Change the data type of "max year"  to "date" and convert into into "discrete" and "dimension".

3. Create year wise sales: { FIXED YEAR([Date]),[Store name]:sum([Sales])}

4. Calculate Flag :

if YEAR([Date])<> YEAR([max year]) and [year wise  sales]=0

then "new" else "old" END

• ###### 2. Re: Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

This seems to be correct but I still wonder how to adjust the logic "Last 3 years" into "Last n years" ?

• ###### 3. Re: Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

Hi Saharat,

to incorporate last 3 years:

1. [Last 3 years] = DATEADD('year',-3,[max year])

2.  modify Flag =

if (YEAR([Date])< YEAR([max year]))

AND (YEAR([Date])>=YEAR([Last 3 years]))

and [year wise sales]=0

then "new" else "old" END

• ###### 4. Re: Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

Please kindly see screenshot below since the calculation "Is Newly Open" divide store into 2 status.

Drill down for store AMR

• ###### 5. Re: Identify newly opened store by if its sales in last 3 years (2015 and earlier) is NULL

Can you please share your data. I am working on 10.2 so not able to open your .twbx file.