7 Replies Latest reply on Oct 9, 2015 3:20 PM by sccardais

    Help Creating a Report

    sccardais

      I'd like to create a report showing New Orders and Cancelations by Year - Month for 7 different products.

       

      My table ORGS has fields identifying the Product, Date_Ordered and Date_Canceled. It also has separate calc fields converting Date_Ordered and Date_Canceled to the YYYY_MM format as shown below. For each product, I want to show the count of orders received that month and the number of cancelations received. In addition, I'd like to show the cumulative number of orders received (column C), Cumulative number of Cancelations (column E) and the cumulative cancelation rate (column E / column C).

       

      I'm having trouble consolidating the data to create this report because there isn't a single Report Period field to sort or match.

       

      Am I missing something obvious here?

      TEMP_Count_by_Year_Month.png

        • 1. Re: Help Creating a Report
          erolst

          Is every record in that table an order, unless it has a Date_Canceled, which makes it a cancellation?

           

          And do you want to aggregate a record that is both an order and a cancellation in both groups?


          If the answer both questions in the affirmative, then you probably need a new table where each record is a date for an order or a cancellation of a product …

          • 2. Re: Help Creating a Report
            sccardais

            To answer your question - every record is an “Organization” (Table name = ORGs) with a unique Org_ID and a value in one OR MORE of these fields:

            Date_FreeTrial

            Date_Order

            Date_Cancel

            Every record with a value in Date_Cancel also has a value in Date_Order. Some records only have a value in Date_FreeTrial. Each of these dates have corresponding calc fields that reformats the date to YYYY-MO (e.g. Year_MO_FreeTrial =2015-07). I’m not sure if I needed to create this separate field rather than simply changing the format of the base field but I did.

             

            I’ve made some progress since my post this morning but still have questions as described below. I created a separate table YEAR_MO with 168 records starting with 2004-01, 2004-02, etc.). I created a layout based on this table that relates to 3 TO’s based on ORGs as shown below.

             

             

             

            This gives me the three base calcs that I want as shown in screenshot below.

             

            I would like to calculate the conversion 5 and Cancel % for each period. Conversion % = Orders / Free Trials. Cancel % = Cancels / Orders but only for the Year.  Can you tell me how to do this? Currently, the values under Free Trials, Orders and Cancels are calc fields that count the number of related records in the three TO’s related to YEAR_MO. This may not be the correct structure given that I want to use them to calculate Conversion % and Cancel %

             

            Thanks very much for your help.

            • 3. Re: Help Creating a Report
              sccardais

              UPDATE: I answered some of my own questions but now have one more issue. In the green box below, I want to show a percentage based on the two cells to the left in the Trailing Grand Summary part. I’m getting accurate results in the blue and red boxes but cannot get an accurate overall result for the green area.

               

               

               

              I want to show the overall Conversion % in the found set. I’m assuming this should be a Trailing Grand Summary part.

               

              Free Trials = count of related records to TO match on Year_Month

               

              Orders = count of related records to TO match on Year_Month

               

              Conversion % highlighted in blue = countOrders / countFreeTrials

              Conversion % highlighted in red = GetSummary(s_SumOrders.Year) / GetSummary (s_SumFreeTrials,Year)

               

              The Conversion %’s results are correct but I cannot get the correct result for the Trailing Grand Summary. The calc based on GetSummary that gives accurate results for the sub summary part when sorted by year does not give the correct result in the Trailing Grand Summary part.

               

              The value in the red box below should be 32.26% (4,031 / 12,496). Instead the value is the result of the last sub summary row “Totals for 2015”. Likewise, the values in the green box (calcs using GetSummary) are also from the last row in the sub summary part. The value in the green box should be 47.36%

              • 4. Re: Help Creating a Report
                erolst

                It may be me, but I don't see any screenshots (though there is some extra space between paragraphs).

                 

                btw, you are right in that you a calculated unique date per group; simply changing the display format wouldn't work.

                 

                Here's another thing: you don't need that extra table; you can define three self-joins if you create three group calculations, like

                 

                cDateTrial = date_trial - Day ( date_trial ) - 1

                cDateOrder = date_order - Day ( date_order ) - 1

                cDateTrial = date_cancellation - Day ( date_cancellation ) - 1


                then create

                Table::cDateTrial = Table_selfByYearMonth::cDateTrial

                etc.


                On the other hand, you can do all those required things using just a single table if you have


                Companies  --< Events, with id_company, date, type (Trial, Order, Cancellation)


                which would also allow you to process recurring customers.

                • 5. Re: Help Creating a Report
                  sccardais

                  UPDATE 2: Appreciate the suggestions on the self join. I'll look at them later.


                  My explanations may not have made any sense without the screenshots. Hope the screenshots came through this time. I'm having trouble showing a calculation in a Trailing Grand Summary as "hopefully" shown in the screenshots and accompanying explanations below. Wondering what type of field to use and perhaps what type of layout part to use to show the Conversion % and Cancel % for the entire group of found records. The % fields in the sub summary parts are correct but neither work when placed in the Trailing Grand Summary.

                   

                  UPDATE 1: I answered some of my own questions but now have one more issue. In the green box below, I want to show a percentage based on the two cells to the left in the Trailing Grand Summary part. I’m getting accurate results in the blue and red boxes but cannot get an accurate overall result for the green area.

                  SS1.png

                  I want to show the overall Conversion % in the found set. I’m assuming this should be a Trailing Grand Summary part.

                   

                  Free Trials = count of related records to TO match on Year_Month

                   

                  Orders = count of related records to TO match on Year_Month

                   

                  Conversion % highlighted in blue = countOrders / countFreeTrials

                  Conversion % highlighted in red = GetSummary(s_SumOrders.Year) / GetSummary (s_SumFreeTrials,Year)

                   

                  The Conversion %’s results are correct but I cannot get the correct result for the Trailing Grand Summary. The calc based on GetSummary that gives accurate results for the sub summary part when sorted by year does not give the correct result in the Trailing Grand Summary part.

                   

                  The value in the red box below should be 32.26% (4,031 / 12,496). Instead the value is the result of the last sub summary row “Totals for 2015”. Likewise, the values in the green box (calcs using GetSummary) are also from the last row in the sub summary part. The value in the green box should be 47.36%

                   

                  SS2.png

                  • 6. Re: Help Creating a Report
                    erolst

                    sccardais wrote:

                    Likewise, the values in the green box (calcs using GetSummary) are also from the last row in the sub summary part.

                     

                    That's correct because it is a calculation field which value “belongs” to a record – in this case the last one because you have a trailing grand summary.

                     

                    sccardais wrote:

                    The value in the red box below should be 32.26% (4,031 / 12,496).

                     

                    So create a calculation field sSumOrders / sSumFreeTrials * 100 and place that into the grand summary.

                    • 7. Re: Help Creating a Report
                      sccardais

                      Thanks. I missed the obvious. Worked perfectly.

                       

                       

                      Sent from my iPad