7 Replies Latest reply on Dec 7, 2016 12:12 AM by Norbert Maijoor

# order date convert into intervals of 15 days

Hi guys,

Iam making a Cash-flow dashboard and i want to make dates or convert exact date into interval of 15 days, like Aug1- Aug 15, Aug 16- Aug30. so Iam stuck at this point. I want to shows these intervals on  column shelf and show data for different measures accordingly. Kindly help.

Regards

Vishnu

• ###### 1. Re: order date convert into intervals of 15 days

Hi Vishnu

Find my approach as reference below and stored in attached workbook version 9.3

• ###### 2. Re: order date convert into intervals of 15 days

Hi Vishnu and Norbert,

Creating group was the good idea but I am looking it from more operational point of view. One has to always update the group as and when the data gets added to the group. I think following approach would be much better if considered which will resolve the manual grouping. I will try to optimize the calculation so that it should take the max of the month for the 16-30/31st part of group. Kindly refer to the screenshot below.

I will attach the workbook after getting the exact end date for the month.

Thanks and Regards,

Ashish Chaudhari

• ###### 3. Re: order date convert into intervals of 15 days

Ashish,

You got a valid point:). Appreciated!

• ###### 4. Re: order date convert into intervals of 15 days

Hi Everyone,

Please find the below approach. Looks bit stupid.

if (DATEPART('day',[date]))>=1 and (DATEPART('day',[date]))<=15 THEN "1-15"

ELSE "16-"+

(if DATEPART('month',[date])=01 or DATEPART('month',[date])=03 or DATEPART('month',[date])=05 or DATEPART('month',[date])=07 or DATEPART('month',[date])=08

or DATEPART('month',[date])=10 or DATEPART('month',[date])=12 THEN "31"

ELSE "30"

END)

END

Thanks and Regards,

Ashish Chaudhari

• ###### 5. Re: order date convert into intervals of 15 days

Hi,

I think you can obtain the number of days in month like this:

datepart('day'

)

right?

or

datediff('day', datetrunc ('month', [Order Date]), dateadd ('month',1,datetrunc ('month', [Order Date])))

• ###### 6. Re: order date convert into intervals of 15 days

Hi Ashish and Norbert,

Thank-you so much guys, given solution resolved my problem. Appreciated !!!

Regards

Vishnu

• ###### 7. Re: order date convert into intervals of 15 days

Goodmorning Vishnu,

You are welcome.