3 Replies Latest reply on Apr 1, 2018 12:11 PM by Norbert Maijoor

# create a new dimension

Hello,

can you assist with the below question:

I have a dimension with the following data as below with various measures tied to it like sales, costs etc. how can I create a calc field to break down the dimension into desired values. For example, I want a dimension which shows only the last quarter of the data which is FY19Q1 and another dimension to show the value prior to it. Finally, my goal is to create dimension using calculated fields to identify the latest quarter, prior quarter etc. Can the level of detail expression help with this problem?

Fiscal Quarter

FY18Q1

FY18Q2

FY18Q3

FY18Q4

FY19Q1

FY19Q2

As per the workbook attached. I can calculate the current quarter based on the current quarter flag field which is available in my original data source, how can I calculate the value for "previous quarter" field to be FY18Q4 for the dimension "Previous Quarter" in this given scenario.

Please find attached the workbook. thanks !!

• ###### 1. Re: create a new dimension

Hi Vijay,

Find my approach as reference below and stored in attached workbook version 10.5 located in the original thread

1. NM D0. Date:

DATE(if [Quarter]='FY18Q1' then #1-1-2018#

elseif [Quarter]='FY18Q2' then #1-4-2018#

elseif [Quarter]='FY18Q3' then #1-7-2018#

elseif [Quarter]='FY18Q4' then #1-10-2018#

elseif [Quarter]='FY19Q1' then #1-1-2019#

END)

2. NM D0. Date (Quarter):

3. NM D1. Current Quarter Flag: if datediff('quarter',[NM D0. Date],today())=0 then "Current Quarter" END

4. NM D2. Previous Quarter Flag: if datediff('quarter',[NM D0. Date],today())=1 then "Previous Quarter" END

5. Drag the required objects to the indicated locations

Regards,

Norbert

• ###### 2. Re: create a new dimension

Hello Norbert,

thanks, much for your reply, I am not able to find the workbook attached. Can you please attach the workbook to this post, thanks !!

• ###### 3. Re: create a new dimension

Vijay

The workbook is attached

Regards,

Norbert