AnsweredAssumed Answered

Cross Tab Report of Quarterly Percentages

Question asked by NaturSalus on May 14, 2013
Latest reply on May 16, 2013 by 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

      

      

Outcomes