8 Replies Latest reply: May 15, 2014 10:15 AM by Olly Bond RSS

    Calculated field question - need first and last date values

    Rebekah T

      I am trying to find a way to create a "processed thru" calculated field, using the first and last dates in one of my columns, because the sort order is NOT by date, and I want the information to be available in the header of the Excel export. If I can pull the most recent date and the most distant date, conctatenate them, and use the "first value in the field" (they would all be the same range for the report, after all) in the header, I would be all set. I am ok with pulling the first and last days of the month, but if the date range is not over the span of a month, my header data is then LYING - Bad Header!

      What do you think? Can the patient be saved, Doctor?

      Thanks,

      Rebekah

      BTW - this works for the entire mo:

      [FONT="Courier New"]Concatenate_XL(Dtoc(Start_Of_Current_Month())," to ",Dtoc(End_Of_Current_Month()))[/FONT]

        • Calculated field question - need first and last date values
          Olly Bond

          Hello Rebekah,

           

          Yes, you can fix this in Monarch, with an if() expression to handle the cases where the Month() value of a date is different.

           

          Best wishes,

           

          Olly

            • Calculated field question - need first and last date values
              Rebekah T

              Thanks, Olly - I don't quite grasp this, so I will play around some more, using the if(). What I need to see is, if, for example, I pull reports from 2/12/13 to 2/20/13 that date range "2/12/13 to 2/20/13" shows in my calculated field (and report header) - Not "2/1/13 to 2/28/13" - if I can do that with the If statement, I am all for it. I will give it a try.

              Thanks very much -

              Rebekah

                • Calculated field question - need first and last date values
                  Grant Perkins

                  Rebekah,

                   

                  Are you grapping this data for a database? If so is there any way to also record the date selections used?

                   

                  If it is a report or an extracted file does the report have the selected dates on it somewhere?

                   

                  One further question.

                   

                  Mostly reports are run for a specific period but that does not mean to say that there ae always transactions (for all categories that might later be analysed) for each day of the period. If the first or last day of the period being reported has no transactions to report using the transaction dates may not always give the period expected by, say, auditors even if the operational management are happy with the approach.

                   

                  This is clearly not an answer to your specific question but may be something that has to be considered at some point as you forward the output for consumption.

                   

                  If you can always be sure that there is at least one transaction (possibly at least one on every likely report sub-grouping?) for the start and end day you may not need to spend much time considering this.

                   

                  Grant

                    • Calculated field question - need first and last date values
                      Rebekah T

                      Hi Grant -

                      I am creating an Excel file from a text report. I would like to enter the date range in the header to show what date range is included in the report. I was hoping to create a calculated field that would look at the column of all included report dates and pull a min and a max date - I thought that once I gathered that information, I would merely concatenate the resulting values as stated in my first posting. Once I had a field that contained the necessary info, I could included it as a "first value" in the header (since the entire calculated field column would have the same info as far as first and last dates go).

                      Using the formula Concatenate_XL(Dtoc(Start_Of_Current_Month())

                      ," to ",Dtoc(End_Of_Current_Month())

                      I can obtain a result that pulls the real first and last days of the month (Ex:03/01/2013 to 03/31/2013) but that is not accurate when my date range really consisted of 3/18 thru 3/22...and I am not sure how to utilize the If() function as Olly recommends...

                      Any additional assistance would be appreciated!

                      Thanks very much,

                      Rebekah

                        • Calculated field question - need first and last date values
                          Grant Perkins

                          Hi Grant -

                          I am creating an Excel file from a text report. I would like to enter the date range in the header to show what date range is included in the report. I was hoping to create a calculated field that would look at the column of all included report dates and pull a min and a max date - I thought that once I gathered that information, I would merely concatenate the resulting values as stated in my first posting. Once I had a field that contained the necessary info, I could included it as a "first value" in the header (since the entire calculated field column would have the same info as far as first and last dates go).

                          Using the formula Concatenate_XL(Dtoc(Start_Of_Current_Month())

                          ," to ",Dtoc(End_Of_Current_Month())

                          I can obtain a result that pulls the real first and last days of the month (Ex:03/01/2013 to 03/31/2013) but that is not accurate when my date range really consisted of 3/18 thru 3/22...and I am not sure how to utilize the If() function as Olly recommends...

                          Any additional assistance would be appreciated!

                          Thanks very much,

                          Rebekah[/QUOTE]

                           

                           

                          Rebekah,

                           

                          What does the original text report look like?

                           

                          Is it a standard "green bar" type of format with a page header, some lines and maybe a footer or two? Or is it something else?

                           

                          It would be usual (but not guaranteed) for a text based report to have its selection criteria reported somewhere. Hopefully that would include the start and end dates although, again, that is not guaranteed.

                           

                          If the input report has the information needed I would seek to use it before looking for ways to pick the dates from the detail records. For the reasons I mentioned before.

                           

                          If you are forced to get the dates from the detailthen things change somewhat and a multi-step analysis may be required.

                           

                          Is your resulting Excel file just using Excel to hold something that is meant to look like a printed "report" rather than a working spreadsheet? Or am I not thinking this through very well?

                           

                           

                          Grant

                            • Calculated field question - need first and last date values
                              Rebekah T

                              Hi Grant -

                              It is a standard green bar report - I am pulling multiple reports with a range of dates and trapping the process thru date from each report header - giving me a column containing all of the process thru dates for the reports selected. We have a report standard for our Crystal output that requires a range to be listed in the header, which I am trying to mimic.

                              I have been playing outside of Monarch to see if I can apply a fix on the Excel side - using the Min and Max on a specified range. Maybe I can utilize the Excel sheet as a template, and just export the report data from Monarch. I have no idea whether it will work, and it is hard to find the time required to play with it properly. Especially since once I get started, I don't want to stop. Playing with formulas is much more enticing than real work, it seems. I will post if I have any incredible breakthroughs. Thanks for your interest in this process!

                              Rebekah

                                • Calculated field question - need first and last date values
                                  Grant Perkins

                                  Hi Rebekah,

                                   

                                  Olly may have some thoughts in this but my initial reaction from your latest description would be close to what you seem to have been heading for.

                                   

                                  Also to create something that should be a re-usable concept for a number of different input. (It might be more useful that way?)

                                   

                                  I'm thinking along the lines of a model that only analysis the Report/Page headers for each report you load as a set and extracte the start and end date ranges. You can then sort (or MIN/MAX) the the 2 dates in each range for the entire set, grab the start and end dates that you need and export to an Excel spread sheet as a "Header" (in a named range of the sheet.)

                                   

                                  Then, using the same report set, do the main analysis and export to the same sheet as an append. (You could also use two different sheets as 'data sheets' and then have them combined within Excel. This might be easier that try to get export formats to play nicely with each other.)

                                   

                                  If you set up both extracts as projects and then create a processing script to run both projects in sequence you should be able to automate the process.

                                   

                                  Once created it should then be a relatively simple job to adapt the script and concepts to additional similar requirements should that be useful or necessary.

                                   

                                  I think you may be very close to arriving at that point already but if not I'll try and expand on the description if you want me to - unless anyone else gets in there first.

                                   

                                  Grant