This content has been marked as final. Show 4 replies
Does this help:
Use the between function with SQL as the value
TIMESTAMPADD(SQL_TSI_MONTH, -1, TIMESTAMPADD(SQL_TSI_DAY, -(DAY(CURRENT_DATE)-1), CURRENT_DATE))
TIMESTAMPADD(SQL_TSI_DAY, -(DAY(CURRENT_DATE)), CURRENT_DATE)
These are the 1st of last month and the last day of last month.
Although the TCV will be the current value not last months value (if that makes sense)
Thanks for the reply, but I havent fully understood this, are you to break it down a bit more for me please?
Also when you say the TCV value will be for the curent month, what do you mean because I thought we're saying between 1st day of last and last day of last month?
Put a filter on the data field such as opty close date.
Set the operator to Is Between
You will see 2 boxes that say Value, this needs to be changed to SQL to accept the formula s I gave you (I can't remember how off the top of my head)
Paste in the first line into the top box and the 2nd line into the 2nd box.
Now when you run it, it will return Optys with a close date of the 1/4/2011 and 30/4/2011 (as today is 4/5/2011) but the TCV will be the value in the records today not that value it was back in April as the reports only run on current values.
Does this help?
Thanks for the reply. So Can you just confirm, are you saying that within analytics there is no way for me to get the TCV value of an opportunity as of last month or last week? I put together a report to try and show the TCV value from last week and I used a filter with the column formula for my TCV value, my formula was; FILTER("Historical Sales Stage".Revenue USING (Opportunity."Close Date" >= VALUEOF("LAST_WEEK_BEGIN_DT"))), but from what you are saying I dont think this can work and I havent really had time to test it out properly. I then had another TCV column with a filter of; FILTER("Historical Sales Stage".Revenue USING (Opportunity."Close Date" >= VALUEOF("CURRENT_WEEK_BEGIN_DT"))). At the moment I'm getting the same value which could be because I havent changed any data or like you say you can only report on current values. What do you think?