4 Replies Latest reply on May 6, 2011 10:16 AM by 856944

    Last Month's data

    856944
      How do you report on last months data. I know there is a session variable for current month, i.e. VALUEOF(NQ_SESSION.CURRENT_MONTH), that I've used in a column formula to filter the data, but there doesnt seem to be one for last month, so how do I get around this? I'm creating a report for Sales Pipleline and trying to show the TCV for this month and last month in order to compare the two. Some assistance please?
        • 1. Re: Last Month's data
          alexn (crmnow)
          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)

          cheers
          Alex
          • 2. Re: Last Month's data
            856944
            Hi alex,
            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?

            Regards
            • 3. Re: Last Month's data
              666726
              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?

              cheers
              Alex
              • 4. Re: Last Month's data
                856944
                Hi Alex,
                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?

                Regards