7 Replies Latest reply on May 16, 2013 3:26 AM by NaturSalus

    Cross Tab Report of Quarterly Percentages

    NaturSalus

      Title

      Cross Tab Report of Quarterly Percentages

      Post

           Hello,

            

           I need to calculate the % of Items closed (= # items closed / total # of items) per quarter and create a cross tab report.

           The first idea that came to my mind was to use the setup of a cross tab report. Something like the following:

            

      Quarterly Closed Items Report

            

           Year: 2012

           Quarter----------------------------------------Q1------Q2------Q3------Q4------Total Year

           % of Closed Items per Quarter------40%----55%----80%----100%-----68.8%

            

           where

           % Closed Items per Quarter = 100*(total number of items closed in a quarter) / (total number of items in a quarter)

            

           But the typical cross tab setup using filtered portals based on a self join relationship doesn't work because the use of summary fields in the Item table doesn't discriminate between different quarters. In a word, it gets the cumulative % of items closed per year, and not the % items closed per quarter for each year.

            

           My current setup is the following:

            

      Table

           Item

            

      TOs

           Item

           AllItem

            

      Relationships

            

      Item::__kp_Item X AllItem::__kp_Item

            

      Fields

            

           The item status (Closed or Open) of each Item record is entered in the Item::ItemStatus field.

      The "number of items" is calculated using the following field: Item::ItemCount (Summary; Count of __kp_Item)

      The "number of items closed" is calculated using the following fields:

      Item::ItemClosed (Calculation; Case (ItemStatus = "Closed"; 1; 0)

      Item::CountItemsClosed (Summary; Total of ItemClosed)

            

      Layouts

      Q_%Item_Closed layout based on the Item table

            

           This layout has the following two sub summary parts:

            

      The Sub-summary by DateItemYear (leading) Part

           This part contains:

      the field z_DateItemYear (calculation; Year (ItemDate); caclulation result is number)

           the text headings Q1, Q2, Q3, Q4, <<z_DateItemYear>>Total.

            

      The Sub-summary by DateItemYear (trailing) Part

           This part contains:

           5 portals.

           Every portal refers to the AllItem TO.

            

           Each portal is filtered to the adequate quarter. So the filter in the portal for the Q1 is:

      AllItem::z_DateItemYear = Item::z_DateItemYear and

           AllItem::z_Quarter_number = 1

      Each portal has just one field referenced to the AllItem TO.

      Obviously any field from the Item table that calculates the % of items closed gives the same value which is the cumulative % of closed items.

            

           I am sure that getting percentages per quarter and putting on a cross tab report is a mundane task in FM and this has been addressed many times before. But since I haven't been able to track the right post I am asking for help about how to set it up.

           Thanks,

            

           natursalus

            

            

        • 1. Re: Cross Tab Report of Quarterly Percentages
          philmodjunk
               

                    But the typical cross tab setup using filtered portals based on a self join relationship doesn't work because the use of summary fields in the Item table doesn't discriminate between different quarters. In a word, it gets the cumulative % of items closed per year, and not the % items closed per quarter for each year

               If you place the summary field inside the portal row, this is not the case. The total shown will be based only on the related records that pass through the portal filter--in otherwords only those records for the specified quarter and year.

               I would use a year field to link only to records for the specified year and then filter the portals by quarter to limit the related records to just those of a specific quarter.

          • 2. Re: Cross Tab Report of Quarterly Percentages
            NaturSalus

                 Hello Phil,

                 Thanks for lookin ginto my question.

                 I am afraid that I am not being able to pull this out.

                  

            I would use a year field to link only to records for the specified year

                 By this should I understand that I need to create another TO of the Item table, for example ItemPerYear TO and relate it back to the Item TO as follows:?

                 Item::__kp_Item = ItemPerYear::__kp_Item

                 AND

                 Item::z_DateItemYear = ItemPerYear::z_DateItemYear

                  

                 I understand the rationale of the tab cross report setup when dealing with counts but not with percentages.

                  

                 I am wondering if my calculations make any sense at all.

            The item status (Closed or Open) of each Item record is entered in the Item::ItemStatus field.

                  

                  
                 The "number of items" is calculated using the following field: Item::ItemCount (Summary; Count of __kp_Item)
                  
                 The "number of items closed" is calculated using the following fields:
                  
            Item::ItemClosed (Calculation; Case (ItemStatus = "Closed"; 1; 0)
            Item::CountItemsClosed (Summary; Total of ItemClosed)

                  

                 My main problem is about how to translate the following calculation to FM:

                 % Closed Items per Quarter = 100*(total number of items closed in a quarter) / (total number of items in a quarter)

                 several possibilites:

                 Item::cPercentageItenClosed (Calculation; = GetSummary ( ItemCount ; CountItemsClosed ) / ItemCount

                 or

                 Item::ItemClosed (Summary; Fraction of Total of ItemClosed)

                 or

                 %ItemClosedYear (Calculation; =100 * (CountItemsClosed / ItemCount)

                 or

                 %ItemClosedQuarter (Calculation; 100 * (CountItemsClosed / TotalCountQuarter)

                  

                 Another  doubt is the layout setup.

                 Should it be the standard cross tab setup 

                 sub_summary by z_DateItemYear (Leading)

                 sub_summary by ItemClosed (Leading)

                 sub_summary by z_DateItemYear (Trailing)

                 or a shorther version?

                  

                 Finally comes the portals for each quarter and totals. To which TO do they refer to and which field do they have?

                  

                  

            • 3. Re: Cross Tab Report of Quarterly Percentages
              philmodjunk

                   Well it would take 4 new TO's one for each quarter, but you could use relationships like this:

                   Item::__kp_Item = ItemPerYearQ1::__kp_Item

                   AND

                   Item::z_DateItemYear = ItemPerYearQ1::z_DateItemYear

                   AND

                   item::constQ1 = itemPerYearQ1::z_DateItemQuarter

                   were you'd define 4 constQ# fields as calculations returning the values 1, 2 ,3 and 4 for the four quarters.

                   Then you can use each relationship for each year in 4 calculation fields to compute your percentages.

              • 4. Re: Cross Tab Report of Quarterly Percentages
                MikhailEdoshin

                     There's a simple way to create cross-tab reports with repeating fields. Create a calculated repeating field with four repetitions, one for each quarter. Use the following formula:

                     Let( [

                       quarter = < calculate quarter of date, don't forget to use Extend() >;

                       if ( quarter = Get( CalculationRepetitionNumber );

                          Extend( < item status field > ) ) )

                     As a result each repetiition will show true or false depending on whether the item was closed.

                     Create a summary field to average these statuses and make sure to summarize each repetition individually. You'll get a nice summary with four repetttions, one for each quarter.

                     Here's an example: https://www.dropbox.com/s/39zyk94fl6si3wp/Report.fp7

                • 5. Re: Cross Tab Report of Quarterly Percentages
                  NaturSalus

                       Hello Mikhail,

                       Thank you for your directions and your demo file.

                       Your approach is brilliant. However, doesn't quite fit my needs.

                       I need to compute % per year and your demo doesn't discriminate bewteen years, I would say.

                       Let me explain myself with an example.

                       If I process the following data:

                       Date------------Status---Quarter

                       10/01/2012----1------------1

                       10/02/2012----1------------1

                       15/02/2012----0------------1

                       13/04/2012----1------------2

                       17/10/2012----1------------4

                       08/11/2012----1------------4

                       13/02/2013----0------------1

                       13/04/2013----0------------2

                       15/05/2013----1------------2

                        

                       I get with your current setting:

                        

                       Quarter

                       Q1-----------Q2-----------Q3-----------Q4-----------Year

                       50%---------67%------------------------100%---------67%

                        

                       Which is correct if you are computing together the years 2012 and 2013 but not if you need to compute the quarterly values per year as follows:

                        

                        

                       Year: 2012

                       Q1-----------Q2-----------Q3-----------Q4-----------Year

                       67%---------100%--------------------100%---------83,3%

                        

                        

                       Year: 2013

                       Q1-----------Q2-----------Q3-----------Q4-----------Year

                       0%---------50%-----------------------------------------33,3%

                       As I said before, brilliant approach and thanks for your input.

                        

                       natursalus

                  • 6. Re: Cross Tab Report of Quarterly Percentages
                    MikhailEdoshin

                         This is easy to achieve: simply add a new subsummaries when sorted by year and copy the summary fields there. I've updated the example to show two years:

                         https://www.dropbox.com/s/39zyk94fl6si3wp/Report.fp7

                          

                    • 7. Re: Cross Tab Report of Quarterly Percentages
                      NaturSalus

                           Guy you are fast....

                           Great approach!

                           Many thanks,

                           natursalus