AnsweredAssumed Answered

Cross Tab Report of Quarterly Percentages

Question asked by NaturSalus on May 14, 2013
Latest reply on May 16, 2013 by NaturSalus


Cross Tab Report of Quarterly Percentages




     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%



     % 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:











Item::__kp_Item X AllItem::__kp_Item




     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)



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.