10 Replies Latest reply on Jul 17, 2018 4:03 AM by Naveen B

# Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Hi all

Let's focus on 2 dimensions which are Date, Store name and Sales as measure.

I want to identify which stores are "New" by following logic.

If sales in 2016 of that store is 0 and sales in 2017 is not 0 then that store is "New" (Since that store is just opened on last year which is 2017)

If sales in 2016 and 2017 of that store is 0 then that store is "Closed" (Since that store is no longer active)

else "Old" (Since that store has been opened for 2 years or older)

Please kindly share your ideas or you may revised my attached workbook under dimension call "Is "New","Old" or "Closed""

• ###### 1. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Saharat Sakulsaowapakkul

Regards

Rosyatul

• ###### 2. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Attached

• ###### 3. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Hi Sharat,

Please create a calculated field similar to this logic.

2016 Sales

If Year(Date) = 2016 then sum(sales) end

2017 Sales

If Year(Date) = 2017 the sum(sales) end

New/Old/Closed Logic

If [2016 Sales] = 0

then

(

If

[2017 Sales] = 0

then 'Closed'

else 'New'

)

else 'Old' end

Hope this helps.

• ###### 4. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Thank for your help but your logic does not work.

It divide 1 store into 2 status as below

• ###### 6. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Hi Saharat,

there is no sale happened for the below stores make it as Closed

Sale happened in 2017 and not in 2016 so treated as new

what would be status if there is any sale happen in previous years

Used this calculation to arrive at the above point

Kindly let me what would be the status if there is sales happened in previous years so that i will update the code accordingly

Mark this answer as helpful or correct if it helps or resolve the issue

• ###### 7. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Please check this workbook, maybe it can solve your problem

Regards

Rosyatul

• ###### 8. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

Thank for your kind but status "Closed" is not working

• ###### 9. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

The right status of your "NA" is "Closed"

However, could you please kindly adjust the "Closed" logic from [Every sales in each dimension are 0]

into logic [the sales in MAX year (2017) is 0] instead ?

I want store EBZ, HO1, HO1 Not Specify, HO2, HO3, HO4 become "Closed"

You are so genius, even I tried to type following your calculation, I still did not get it.

I just need you to adjust to calculation since I totally cannot do it.

So many thank you.

• ###### 10. Re: Create new dimension to filter stores whether it is "New","Old" or "Closed" by its sales (measure)

hi Saharat,

Check the screenshots below

Whenever 2017 is 0 or whole sale is 0 then it is closed

Whenever 2017 have sales and 2016 is zero then new

Whenevr there is sales happened in previous store is old

Calc

Text Calc (To Copy)

IF

MIN({FIXED [Store Name]:MIN([Sales])} )=MAX({FIXED [Store Name]:MAX([Sales])})

or

SUM(

{INCLUDE [Store Name]:SUM({FIXED [Store Name]:SUM(if YEAR([Date])={MAX(YEAR([Date]))} then [Sales] ELSE 0 end)})})=0

then

"Closed"

ELSEIF

SUM(

{INCLUDE [Store Name]:SUM({FIXED [Store Name]:SUM(if YEAR([Date])={MAX(YEAR([Date]))} then [Sales] ELSE 0 end)})})<>0

and

SUM(

{INCLUDE [Store Name]:SUM({FIXED [Store Name]:SUM(if YEAR([Date])={MAX(YEAR([Date]))}-1 then [Sales] ELSE 0 end)})})=0

then

"New"

ELSE

"old"

END

kindly mark this correct or helpful if it helps you