1 Reply Latest reply on Sep 12, 2016 8:16 AM by Luciano Vasconcelos

# Calculation Question (week to day, month to day, yesterday, current)

Hi,

I have an issue to calculate the data for Current day, yesterday, week to day, month to day, prior month, others.

I was using this script:

IF DATEDIFF('day', DATE([Milestone Date]), NOW()) = 0 THEN "Current"

ELIF DATEDIFF('day', DATE([Milestone Date]), NOW()) = 1 THEN "Yesterday"

ELIF DATEDIFF('day', DATE([Milestone Date]), NOW()) <= 7 THEN "week to day"

ELIF DATEDIFF('month', DATE([Milestone Date]), NOW()) = 0 THEN "Month to day"

ELSEIF DATEDIFF('month', DATE([Milestone Date]), NOW()) = 1 THEN "Prior Month"

ELSE "Others"

END

But I can only get the data of Current and Yesterday correctly, data for Week to day will exclude Current and yesterday, but it should include current and yesterday.

Same with Month to day, it should include week to day, current and yesterday.

Thanks!

Fan Feng

• ###### 1. Re: Calculation Question (week to day, month to day, yesterday, current)

Hi.

It happens because your calculations are conflicting one another.

I suggest you to create one calculation for each range and use measure names instead built a range dimension.

Ex:

Current

Sum(IF DATEDIFF('day', DATE([Milestone Date]), NOW()) = 0 THEN Value End)

Yesterday

Sum(IF DATEDIFF('day', DATE([Milestone Date]), NOW()) = 1 THEN  Value End)

....