7 Replies Latest reply: May 15, 2014 10:10 AM by Chickenman _ RSS

    Is there a straightforward way of dealing with this?

    Chickenman _

      I have created a report of sales generated from a mainframe - date range 2/1/2010 thru *TODAY (4/11/2011 as of this writing).


      Typical fields were trapped with a Detail Template: Salesperson, customer, date, order#, item, amount; and the Report Date as a Header.


      The Fiscal Year for this company runs from February 1 through January 31, so a calculated field lists the Fiscal Year (e.g.[/I] 2009, 2010...) out into the future as the final model/project will be memorialized on Monarch Data Pump as part of a "poor man's BI" which users will run on demand and the output emailed as Excel.


      The task is to create a summary that compares sales by person/customer as Current YTD versus Prior YTD with a calculated delta for Year-over-year, and that is roughed-out.


      After looking at this for some time now, I am asking the Forum members for guidance. It is not apparent how to create the filter which will recognize sales dates in the ranges 2/1/2011 - 4/11/2011 (FY2012) and 2/1/2010 - 4/11/2011 (FY2011) to properly populate the summary. Obviously, the filter must be generic enough so that when the report is run, say a year from now, it will properly select ranges 2/1/2011 - (FY2012) and 2/1/2012 - (FY2013).


      Thanx in advance for your help,


        • Is there a straightforward way of dealing with this?
          Olly Bond

          Hello CM


          I'm sure this is doable. My first thought would be to use Julian dates - eg 2011031 as a simple way of getting a handle on the ranges from 20xx031 to today's date.


          I can't write a longer reply now, but happy to look into this in a bit more detail tomorrow if that would help.


          Best wishes



            • Is there a straightforward way of dealing with this?
              Chickenman _

              Hello, Olly.


              Appreciate the fast response. I have a short term handle on this by setting up calculated fields to pull in the Dollar amounts within fiscal years and then filtering = current FY OR If(Date<=ReportDate - 365,1,0). The report model would need an annual update for date range and fiscal years respectively, so would like to hear more about your Julian date approach when you can explore further.


              Best regards,


                • Is there a straightforward way of dealing with this?
                  Data Kruncher

                  Don't overlook Monarch's custom time intervals (under the Options menu) which are the ideal starting place for this type of work. This will make the determination of the fiscal year a breeze, but unfortunately there still aren't any simple ways of building a year-over-year delta calculation.


                  I'd be temped to build an initial summary with a key field or two (without any subtotals and displaying any duplicates), an across key for the fiscal year, labeled as This Year and Previous Year. That might necessitate adding a runtime parameter calculated field for the value of the current fiscal year.


                  Then export that summary and use it as an input for one more Monarch process which calculates the delta amounts and provides the desired final summary.

                    • Is there a straightforward way of dealing with this?
                      Olly Bond

                      Sorry for contributing in postcards rather than writing a letter, but a) thanks Kruncher for the excellent suggestion about the Input Options, I'll check that out, and b) Chickenman, I don't think an across key is going to work as easily as some calculated fields and then using multiple measures in the summary. A field for the value of a single sale in the subtotal of sales to date in the previous fiscal year might look like:


                      = if(=[FiscalToday]-1 .and. <=[JulianPrevious];[SaleValue];0)


                      where JulianSale is a numeric form of the Julian Date of the sale record, and JulianPrevious is a numeric form of today's date less one year.


                      A summary showing Sum() as a measure should begin to give you what you need.


                      Got to dash, but I'll have another look tonight.


                      Best wishes,



                        • Is there a straightforward way of dealing with this?
                          Chickenman _

                          Sandy and Olly, thank you both for replying.


                          The custom Time Interval feature is not useful in our application due to its constraints which in fact distort the needed specifics. I need a year that starts exactly on February 1st and ends exactly on January 31st with a 4-4-5 pattern. The choices in the Time Interval dialog box are 1) The year starts on the first Sunday on or after February 1st; 2) ...Sunday closest to February 1st; 3) ...ends on the last Saturday on or before February 1st; or 4) ...ends on the Saturday closest to February 1st. None of these is the case.


                          What I have created is the following highly truncated summary, the actual full result is some 1600 rows with about 200 customers for 9 salesmen:


                          [FONT=Tahoma][SIZE=2]Salesperson[/SIZE][/FONT][FONT=Tahoma][SIZE=2]Customer[/SIZE][/FONT][FONT=Tahoma][SIZE=2]FY2011[/SIZE][/FONT][FONT=Tahoma][SIZE=2]FY2012[/SIZE][/FONT][FONT=Tahoma][SIZE=2]Delta[/SIZE][/FONT][FONT=Tahoma][SIZE=2]Salesman1[/SIZE][/FONT][FONT=Tahoma][SIZE=2]ABC Company[/SIZE][/FONT]34,1281,655-32,473[FONT=Tahoma][SIZE=2]Subtotal Salesman1[/SIZE][/FONT]34,128[FONT=Tahoma][SIZE=2]1,655[/SIZE][/FONT]-32,473[FONT=Tahoma][SIZE=2]Salesman2[/SIZE][/FONT][FONT=Tahoma][SIZE=2]XYZ Ltd[/SIZE][/FONT]7,60321,49613,893[FONT=Tahoma][SIZE=2]Subtotal Salesman2[/SIZE][/FONT]7,603[FONT=Tahoma][SIZE=2]21,496[/SIZE][/FONT]13,893[FONT=Tahoma][SIZE=2]Totals[/SIZE][/FONT][FONT=Tahoma][SIZE=2]41,731[/SIZE][/FONT][FONT=Tahoma][SIZE=2]23,151[/SIZE][/FONT][FONT=Tahoma][SIZE=2]-18,580[/SIZE][/FONT][/CODE]


                          As mentioned in my initial post, a calculated field looks at the Invoice Date and applies the corresponding fiscal year through a series of nested If Statements, e.g.[/I] FY: If(Date>{1/31/2010} .And. Date<{2/1/2011},2011,If(Date>{1/31/2011} .And. Date<{2/1/2012},2012,0))


                          Then a calculated field creates a column for current and prior fiscal year and fetches sale amount: FY2012: If(FY=2012,Amount,0); FY2011: If(FY=2011,Amount,0)


                          Next, a compound filter composed of If(Date<=ReportDate-365,1,0) OR FY=2012 selects the appropriate records fo current and prior YTD amounts.


                          Finally, the Summary is as shown above, the Delta column simply edited to

                          SUM(FY2012)-SUM(FY2011) with a little coloring to highlight underperforming conditions :cool:


                          Of course this approach is not generic, but as each fiscal year advances it is a simple matter of tickling my Outlook calendar to remind that some minor maintenance is needed to the model to reflect the passage of time.


                          I will look further at your suggestion of converting to Julian dates, Olly, as that may turn this into a fire-and-forget process that will really rock.




                          Edit: I have tried numerous times to post the table, yes between code tags, and it looks WYSIWYG but when saved it is not. Hopefully you get the idea here.

                            • Is there a straightforward way of dealing with this?
                              Olly Bond

                              Hello CM,


                              I think you're almost there - but instead of calculated fields explicitly labelled as FY2011, FY2012 etc, create fields called CYTD and PYTD, and then your approach should work fine. There's an option in the DateToJulian() function to return seven character strings like "2011031", so start with and defined this way from the and Today() functions. You can use instead of Today() if you prefer to keep a historical view tied to the date of the source data.


                              From these Julian dates, working out the involves chopping the string:




                              is if(val(right(;3))<32;val(left(;4))-1;val(left(;4))


                              Now we want to know how far into the FY we are, so we create and fields as val(right(;3)) and val(right(;3).


                              Then, for each row of data in the table, we just need to create the CYTD and PYTD values for it, depending on whether it falls in the current or previous year relative to the reference date.




                              =if(=[ReferenceFY]-1 .and. <=[ReferenceCounter];[Amount];0)


                              Then a summary with sum(), sum() and sum()-sum() as measures should give you what you need.


                              Hope this helps,