9 Replies Latest reply: Oct 29, 2011 12:19 PM by Richard Sheppard RSS

    Set Analysis - evaluating dates using variables



      I would like to create a set analysis, that compares dates outside of the current selection, that are greater than the "MinPeriod" and less than/equal to "MaxPeriod" (where both "MinPeriod" and "MaxPeriod" are variables).


      Please see attached QV doc...


      Anyone have any thoughts on the matter?


      Kind regards,



        • Set Analysis - evaluating dates using variables

          thought something similar to the below would work, but I can't make it work


          sum({CalYearMonth = {>=$(zzMinPeriod)<=$(zzMaxPeriod)}>} Euros)


          Any thoughts?

            • Set Analysis - evaluating dates using variables
              Miguel Angel Baeyens de Arce

              Hi Rich,


              Check this application using variables in ranges and set analysis. Note that the format in CalYearMonth must be exactly the same than the returned by zzMinPeriod and zzMaxPeriod. In regards to use Date fields instead of string periods to get dates right, check this thread.


              Hope that helps.


              Miguel Angel Baeyens

              BI Consultant

              Comex Grupo Ibérica

                • Re: Set Analysis - evaluating dates using variables

                  Hi Miguel,


                  Many thanks for your response!


                  In your reply you identified two further areas for me to investigate: 

                  1) check that the formatting is consistant between the time periods in my data, vs the time periods being returned in my variables

                  2) review the script used in a separate application, which tackles the same objective



                  1) In the revised attachment, I have created some test text boxes to evaluate (when only 1 "CalYearMonth" period is selected), whether the selected month meets a number of criteria based on the variables used (ie does "CalYearMonth" match the max selected date (variable = "zzMaxPeriodThisYear")  , whether "CalYearMonth" is greater than the min date of last year (variable = "zzMinPeriod"), and whether "CalYearMonth" is less than the max date from the previous year (variable = "zzMaxPeriod")


                  The results of the above test return as expected.  Due to this, is it safe to assume that the formatting matches both in my data, and in the created variables?



                  2) Looking at the document posted in one of your previous threads, I have identified the following formula to meet my needs:


                  " Sum({<CalendarDate = {'>=$(=Date(vStartDate))<=$(=Date(vEndDate))'}>}SalesAmount) "


                  As a result of the test performed in part 1) above, have decided (correctly or incorrectly?) to rem(ove the "Date()" function in the above syntax (as I believe the 2 are in the same format).



                  Tailoring the above to my data, I have generated the following syntax:


                  " Sum({<CalYearMonth = {'>=$(zzMinPeriod) <=$(zzMaxPeriod)'}>}Euros) "


                  which doesn't return the expected results...



                  Does anyone have any additional thoughts?


                  Please advise,


                  Kind regards,



              • Re: Set Analysis - evaluating dates using variables

                Hi Rich,


                Attached is your file that will hopefully get you some way to the solution.


                There was a problem with your syntax, you need to add an AND to your set analysis.


                The other issue, as I see it, is that your dates are strings and as such greater than/ less than cannot evaluate. You can use the dual function, or I think the date function to make sure a numerical value is associated to your variables and month/ year fields.


                Hope this helps




                  • Re: Set Analysis - evaluating dates using variables

                    Hi James,


                    Many thanks for your review!


                    Have played with the file and with a few tweaks got what I wanted, so many thanks!


                    Key change that I made, was in the set analysis to remove the current period selections (in the CalYear, FiscalYear and CalYearMonth fields)


                    Other things I tailored were to take account of incomplete years (ie 2011 in the attached, where only 3months data included).  Have updated the attached to show a YTD comparison, and then taking into account not a full year to return complete previous year.


                    Once again, many thanks for your input


                    Kind regards,