3 Replies Latest reply: May 18, 2011 6:24 AM by 763243 RSS

    Daily, Monthly & YTD Numbers

      HI guys,
      I need some help. I need to display/calculate number of records for different types of SR's and different time periods in 4 columns as displayed below

      SR Type  |  Daily  |  30 Days  |  YTD (Year-to-Date)
      ______ ____ _______ _______

      Type A | 10 | 87 | 578
      Type B | 7 | 44 | 234

      Daily = SR's created/opened yesterday i.e. CURRENT_DATE-1
      30 Days = SR's created in last 30 days starting yesterday (no specific months but 30 days )
      YTD = SR's created till yesterday since start of the Fiscal Year (September 1st)

      How do I do it in a single report.
      I have a basic report with the different types and daily numbers running correctly. I tried to do it using a Pivot Table and CASE Statement

      WHEN "Date Opened".Date = CURRENT_DATE-1
      THEN 'Daily'
      WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Date Opened".Date, CURRENT_DATE-1) between 0 and 29
      THEN '30 Days'


      I could not get the formula for the 3rd condition (YTD)

      Is it possible to create a single report for these requirements and should I use a Pivot Table or is some other form better suited to these conditions?

      Please help

      Thanks in advance

      P.S. Was this topic overlooked? Does anyone have an Answer. Help!!!1

      Edited by: Gaurav Shah on May 9, 2011 1:02 PM
        • 1. Re: Daily, Monthly & YTD Numbers
          Hello Gaurav,

          I believe that your clients wants to see SRs that were created in the current year AND in the Daily, Last 30 days and YTD.
          You logic is fine.

          I first applied the filter on the opened date. The filter was YEAR("Date Opened".Date)=YEAR(CURRENT_DATE). This would fetch only SRs of the current year. To apply this formula, click on the fx button of Opened date and replace the exisiting value with YEAR("Date Opened".Date).
          Then click on the filter of the column and Add session variable. In the Session variable add YEAR(CURRENT_DATE).

          You need to use pivot table here. The rows would be the Type A, Type B etc.
          The Columns would be the column having the function to check daily or last 30 days (refer below for the function) *@*
          The measure would be # of Service Requests.

          @CASE WHEN "Date Opened".Date = CURRENT_DATE-1 THEN 'Daily' WHEN TIMESTAMPDIFF(SQL_TSI_DAY, "Date Opened".Date, CURRENT_DATE-1) between 0 and 29 THEN '30 Days' ELSE 'YTD' END

          Let me know if you were able to solve the issue. You can also call me at 91-8197839902, however please note that I am working in Indian Time Zone.
          • 2. Re: Daily, Monthly & YTD Numbers
            Hi Paul,
            thanks for the help but
            for the Year wouldn't this be more efficient? on the fiscal year field under Date Opened Category

            Fiscal Year is equal to or is in NQ_SESSION.CURRENT_YEAR (session variable on fiscal year field)

            with this you don't have to setup any other calculations inside the field formula

            Also I tried out the formula you gave me with a Pivot table
            if i only use the daily part then it shows the correct numbers
            when i use 2 conditions Daily and 30 days - the daily number is correct but the 30 day number is total - daily (since CASE-WHEN works like IF-ELSE therefore this is expected)

            and if i simply use YTD in else condition it returns wrong numbers.

            the sumation of the 3 columns probably shows the correct total but not what i need
            • 3. Re: Daily, Monthly & YTD Numbers
              Hello Gaurav,

              The reports has been developed. I have used normal count and sum method instead of the Case statement. Unfortunately I am unable to paste screen shots in here and I would be sending an email.

              The logic is that when it is YTD, Last 30 days or Daily I consider is as numeric 1. Finally I do aggregation to get the sum. The aggregation can be done at column level (Step 1) or at Pivot Layout (Step 2) Level. I have done it at the Column (Step 1) level. You will find the details in the document.

              I have also developed the same report using Combined Analysis. Let me know if you need the approach also, however, I would require a different communication system other than this forum and emails to explain that. I would be fine with Skype.

              Please check your email for the details.



              Edited by: Paul-CRMIT on May 18, 2011 12:08 AM

              Edited by: Paul-CRMIT on May 18, 2011 4:20 AM