8 Replies Latest reply: Feb 1, 2013 2:58 PM by Oleg Troyansky RSS

    Set analysis year of today

      Hi all

       

      Im trying to make a calculated dimension for a bar chart so that I only show the YearMonths where the year is the current year.

       

      Something like this...

       

      =only({< Year = {"=Year(today())"}>} YearMonth)

        • Re: Set analysis year of today
          Stefan Wühl

          You can just use a calculated dimension like

           

          =if(Year = Year(Today()), Year)

           

          or limit your records with set analysis in your expression aggregation functions, e.g.

           

          =sum( {< Year = {$(=Year(today()))}>} Value)

            • Re: Set analysis year of today

              Follow up question....

               

              Why doesn't this work with a +2 but it will work with a +1? The +2 keeps returning the +1 value even though its not the same.

               

               

              =

              count(DISTINCT{1<EXP_YEAR = {"$(=year(today()))"}
              ,
              EXP_MONTH = {"$(=month(today()+2))"}
              ,
              YHT_STATUS

              -= {'CANCELATION','QUOTE','Quote',' '}>} YHT_POLICY_NBR)
              & ' Policies will expire this month'

                • Re: Set analysis year of today
                  Stefan Wühl

                  Check you bracketing. Do you want to add 2 to the date of today() or to its month?

                    • Re: Set analysis year of today

                      I want to add it to the month

                        • Re: Set analysis year of today
                          Stefan Wühl

                          Then you are probably looking for

                           

                          $(=month(today())+2)

                           

                          You might need to take care of the format of your field EXP_MONTH, though (it shouldn't be a problem if it's numeric, though doing it like this will probably lead into troubles at the end of year (when adding 2 will result in 13, 14 --> not valid month numbers. I would prefer using a timestamp as underlying numerical month number.)

                           

                          Anyway, if this is still not helping you, try posting a small sample qvw and I am sure someone around will get the correct expression pretty fast.

                           

                          Regards,

                          Stefan

                            • Re: Set analysis year of today

                              I cant post due to personal information.

                               

                              Whats odd is that now that the month has changed, I cant get the +1 to work now either.

                               

                              Yesterday (when it was January) the +1 worked to get February data. Now thats its february, it should give march. But now it doesn't work and just gives the current months data.

                               

                              Could this have to do with date formating?

                              • Re: Set analysis year of today

                                looks like I had to use the num() function in the set analysis and as well as in the script to make the fields.

                                 

                                Like you said it will probably cause a problem come year end. Any idea how to avoid this?

                                  • Re: Set analysis year of today
                                    Oleg Troyansky

                                    I see quite a few opportunities for improvement here:

                                     

                                    1. The Year of today and the Month of today are well known upfront, there is no reason to bore the user and calculate those again and again in front of him. Calculate your Calendar in the script, and add flags for Year-To-Date, Month-ToDate, Quarter-ToDate, etc... You can get fancy and calculate this month, next month, 2 months from now, etc... Assign 1 for every date that belongs to YTD, MTD, etc... and 0 ot null() for all other dates.

                                     

                                    2. Once you do, your Set Analysis condition will look very simple:

                                     

                                    CYTD_Flag = {1}

                                     

                                    3. When working with Months, you don't want to use Month Number (1-12) and add or subtract to it - as you realized, you will run into a Year End problem. Instead, always operate with MonthStart() to specify your Months, and use function AddMonths() to move one or more months forward or backward from the current month. This way, you can cross years and centuries safely.

                                     

                                    4. When using Date fields in Set Analysis, you have to provide values in exactly the same date format as the field in question. For this reason, it's sometimes easier to also keep a numeric version of the same field and compare it to a number, rather than chase a specific date format.

                                     

                                    5. Finally, your chart will perform much better if you limit your Expressions with Set Analysis, instead of using Calculated Dimensions.

                                     

                                    So, instead of defining a calculated dimension like:

                                     

                                    =only({< Year = {"=Year(today())"}>} YearMonth),

                                     

                                    use a static dimension YearMonth, and limit your expression with a similar Set Analysis condition:

                                     

                                    sum( {<CYTDFlag={1}>} Sales)

                                     

                                    (assuming that you implemented my earlier recommendations about pre-calculated flags)

                                     

                                    On a large data set, you will fill a huge performance improvement from those changes.

                                     

                                    best,

                                     

                                    Oleg Troyansky