This discussion is archived
11 Replies Latest reply: Sep 17, 2013 6:55 AM by vjbez1

# How to calculate a measure for the current week ?

Currently Being Moderated

Hello all

I am trying to get a calculation of a measure for a current week which is written as, case when date between current date - 7 and current date then 'measure' end but I am getting error saying - cannot be used, please advise how can I write this to work.

Thanks

Ravi

• ###### 1. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Use as below

current_date - 7 and current_date

or else use timestampadd function for those 2 dates

• ###### 2. Re: How to calculate a measure for the current week ?
Currently Being Moderated

I tried the current_date-7 and current_date as I mentioned in my question but it is throwing error. Can you please help me how can I use the timestamp add?

• ###### 3. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Here you go..This should give you what you need ..

• ###### 4. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Thanks for the response I see that

Days difference:

TIMESTAMPDIFF(SQL_TSI_DAY, Time."Day Date",CURRENT_DATE), so what should I use for time.day because I am onlny interested in the past 7 days.

• ###### 5. Re: How to calculate a measure for the current week ?
Currently Being Moderated

this is your formula..

Column 1 between Current_date and TIMESTAMPADD(SQL_TSI_DAY, -7,current_date)

This will help..

Please mark if this helps..

• ###### 6. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Thanks again, i tried the same and I a m getting binary logical operation is not permitted on timestamp date operand.

• ###### 7. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Please copy and paste your formula here..

• ###### 8. Re: How to calculate a measure for the current week ?
Currently Being Moderated

case when "Ship Date". Ship Date" between CURRENT_DATE and TIMESTAMPADD(SQL_TSI_DAY,-7,CURRENT_DATE) THEN "Measure". 'Count" END

• ###### 9. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Am I doing anything wrong here?

• ###### 10. Re: How to calculate a measure for the current week ?
Currently Being Moderated

Any advise can be greatly appreciated.

• ###### 11. Re: How to calculate a measure for the current week ?
Currently Being Moderated

I just used the same formula in my report and it works fine. What is the data type of Ship Date Column? Make sure it is a date and also you are able to compare it with the CURRENT_DATE

#### Legend

• Correct Answers - 10 points