9 Replies Latest reply on Sep 13, 2019 11:16 AM by Sinisa Mikor

    Last 12 Months Report in between years - In Smart Reporting

    Raju Mangali
      Share This:

      Hi All,

       

      I have some challenging tasks, I would like to know your inputs and suggestions please keep some insights here.

       

      Requirement 1) I would like to display the data for last 12 months but in between years. For example I want the report to be displayed data in between 2015 - 2019 then it should be like as follows.

       

      Current month of the year(Sep 2019 - Aug 2015) then display like below and total of columns.

       

      Aug 2015, Sep 2015, Oct 2015 .......   Sep 2016

      Aug 2016, Sep 2016, Oct 2016... .....  Sep 2017

      Aug 2017, Sep 2017, Oct 2017.......... Sep 2018

      Aug 2018, Sep 2018, Oct 2018.........  Sep 2019

       

       

      2) Displaying total columns that would sum the last twelve months.  For example, if the current month of data availability is July, this report should show a column called “July 2019 LTM” which will sum all values from August 2018 – July 2019.

      There would also be other columns in the view for July 2018 LTM, July 2017 LTM.

       

      Kindly provide your suggestions and thank you in advance.

       

       

      Regards,

      Raju M

        • 1. Re: Last 12 Months Report in between years - In Smart Reporting
          Raju Mangali

          Hi All,

           

          Anyone is there on this?

           

           

          Regards,

          Raju M

          1 of 1 people found this helpful
          • 2. Re: Last 12 Months Report in between years - In Smart Reporting
            Abhay Bagalkoti

            Hello Raju,

             

            I am not sure that we can achieve this 100%, especially for getting last 12th month from current on each new row.

            However, the quick think that can work, to have a Year field as section to separate the rows. See example -

             

            You can use the filter with dynamic data i.e.

             

            The other thing is - it will only show values if there is any data else.

             

            See it helps.

             

            Regards,

            Abhay

            3 of 3 people found this helpful
            • 3. Re: Last 12 Months Report in between years - In Smart Reporting
              Sinisa Mikor

              Hello Raju,

               

              since the type of report Abhay illustrates doesn't always show all columns (this can be remedied) and totals aren't directly accessible (this cannot), here's another (probably overcomplicated and possibly infeasible) suggestion -- you could try using calculated field for each of twelve monthly columns and another for yearly total.  Monthly columns might be defined as:

               

              SUM(

                   CASE

                        WHEN

                             YEAR(`form`.`date attribute`) = field_year AND

                             MONTH(`form`.`date attribute`) = field_month

                             THEN 1

                        ELSE

                             0

                   END

              )

               

              to show number of occurrences of certain event each month (e.g. if date attribute is Submit Date of incident, result would be number of incidents submitted during that month).  If you vary field_month and field_year, you'll be able to show as many columns as desired; additional column could show sum of all preceding ones.  In your case first column should use field_month = current month, and field_year = current year - 1, while the last would use field_month = current month - 1, and field_year = current year; also, each successive row should subtract one more year.

              3 of 3 people found this helpful
              • 4. Re: Last 12 Months Report in between years - In Smart Reporting
                Raju Mangali

                Hi Abhay and Sinisa,

                 

                I will provide Fromyear and ToYear fields data dynamically for example like below.

                 

                 

                Please let me know if we can achieve this. For showing the data it's not required for me to show in each row of year but I could show it in each column like as below.

                 

                Only thing I would like to know that how to achieve last 12 months data in between years as shown in my top first post.

                 

                 

                Regards,

                Raju M

                1 of 1 people found this helpful
                • 5. Re: Last 12 Months Report in between years - In Smart Reporting
                  Sinisa Mikor

                  Hi Raju,

                   

                  what you're looking for is possible, and it is easier to display in a sort of calendar page format; with some modifications, it could be used to display results in actual calendar page format (weeks and days) as well.

                   

                  To ease explanation, let's presume that you wish to show number of incidents submitted per month so report will be counting incidents and distributing results according to Submit Date.

                   

                  Start by defining calculated field which will extract year part of Submit Date either:

                  • using YEAR function with Submit Date as Value from predefined formula type, or
                  • using YEAR(`HPD:Help Desk`.`Submit Date`) with Freehand SQL formula type

                  That calculated field will double as legend for reading report so put it either as first or last column.

                   

                  Use similar process to define calculated field which will extract month part of Submit Date:

                  • using MONTH function with Submit Date as Value from predefined formula type, or
                  • using MONTH(`HPD:Help Desk`.`Submit Date`) with Freehand SQL formula type

                  This calculated field should not be added to report, but may be necessary to construct remaining columns.

                   

                  Define twelve calculated fields, one for each month, as

                       SUM(

                            CASE

                                 WHEN

                                      MONTH(`HPD:Help Desk`.`Submit Date`) = <insert ordinal number of month here>

                                           THEN 1

                            END

                       )

                  and add them to report; if you're unable to enter MONTH(`HPD:Help Desk`.`Submit Date`), use previously defined calculated field instead:

                       SUM(

                            CASE

                                 WHEN

                                      Month = <insert ordinal number of month here>

                                           THEN 1

                            END

                       )

                   

                  For yearly totals, simply use count of Incident Number.

                   

                  To restrict results to period viewer is interested in, use Submit Date as filter with operator set to between; in months falling outside chosen period, but inside chosen years, blanks will be displayed which should be harder to misinterpret than zero values.

                  2 of 2 people found this helpful
                  • 6. Re: Last 12 Months Report in between years - In Smart Reporting
                    Raju Mangali

                    Hi Sinisa,

                     

                    Could you please show the results in smart reporting how it would be looks like? Because I haven't see the option for SUM in smart reporting.

                     

                    It would be great helpful for me if you would show this in smart reporting screen shots. It is very important requirement for me to reach this.

                     

                    Looking forward to hear back.

                     

                     

                    Regards,

                    Raju M

                    • 7. Re: Last 12 Months Report in between years - In Smart Reporting
                      Sinisa Mikor

                      Hi Raju,

                       

                      here's a list of calculated fields you'll need; as mentioned before, Month should not be added to report.

                       

                       

                      Blue ones only differ in field name and number after "Month = "; for example, here's how it would look for the 4th month:

                       

                       

                      SUM (represented by capital Greek letter sigma -- S) could be replaced by COUNT (represented by #) in this case because value of one is used as replacement value and count of ones is equal to sum of ones.

                       

                      Results are limited to user-specified time period with a simple filter:

                       

                       

                      which may be beautified by using value list selection and default value

                       

                       

                      SQL Statement, filtering by that default value should look like (do note that last column is simply COUNT(`HPD:Help Desk`.`Incident Number`) renamed to Total)

                       

                      SELECT DISTINCT

                         YEAR(`HPD:Help Desk`.`Submit Date`),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 1 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 2 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 3 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 4 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 5 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 6 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 7 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 8 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 9 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 10 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 11 THEN 1

                         END),

                         SUM(CASE

                            WHEN MONTH(`HPD:Help Desk`.`Submit Date`) = 12 THEN 1

                         END),

                         COUNT(`HPD:Help Desk`.`Incident Number`)

                      FROM `AR System Schema`.`HPD:Help Desk`

                      WHERE (

                         `HPD:Help Desk`.`Submit Date` BETWEEN '09/12/2018 12:00:00 AM' AND '09/12/2019 11:59:59 PM'

                      )

                      GROUP BY

                         YEAR(`HPD:Help Desk`.`Submit Date`)

                       

                      Here are the results when ran on my test sample with this default value.

                      Number of rows will vary depending on selected time period -- report will always show minimum number of rows ("years") needed to cover it.

                       

                      The last (grayed) row shows column totals; it is not necessary for successful run of report, but may be informative -- to display it, adjust summary settings of all desired columns like this:

                       

                      If also you want to differentiate number of incidents by more than date they were submitted on, simply add appropriate column(s) after year column to keep rows arranged by years; for example, to show individual contributions of incident submitters, add Submitter between columns Year and Jan.  In that case, it might look better if duplicates were suppressed for Year, and subtotal without aggregation was shown below that column.

                       

                      3 of 3 people found this helpful
                      • 8. Re: Last 12 Months Report in between years - In Smart Reporting
                        Raju Mangali

                        Hi Sinisa,

                         

                        I am really impressed by this solution thank you very much.

                        It should work for the last 12 months report from current day. What if I want the report for example From Year is: 2016 and ToYear is: 2020 then how do I achieve this? (FormYear and ToYear will vary depends on customer selections)

                         

                         

                        Kindly keep some light how to pass FromYear and ToYear values in between Submit date field.

                         

                         

                        Regards,

                        Raju M

                        • 9. Re: Last 12 Months Report in between years - In Smart Reporting
                          Sinisa Mikor

                          If you've chosen to use advanced filter formatting (pre-defined periods from value lists selection with default value, in this case Today Minus 12 Months), initial filter state should look like this

                           

                           

                          To change time period shown in report, viewer should simply open filter's menu, select one of presented periods and click <Go> to refresh report.  If none of predefined periods is to their liking, they can either:

                          • pick the first item from menu (-- Omit --) to clear both start and end date, which would show counts of all incidents in database that viewer is entitled to peruse, regardless of when they were submitted, or

                          • pick the last item from menu (Custom) and another start and end date picker will show up

                           

                          To display data between years 2016 and 2020, click on upper date field, then click above left calendar to select start year, month and day (01/01/2016), then click on lower date field and above right calendar to select end year, month and day (31/12/2019); once you click apply, filter will be loaded with selected values, so you only need to press <Go> to refresh report.

                           

                          1 of 1 people found this helpful