3 Replies Latest reply: Feb 18, 2013 5:35 AM by Stefan Wühl RSS

    Yearmonth -1 Calculating as 201300 NOT 201212

    chris goudy

      Now that it's January 2013 (201301), my expression below is not calculating correctly because it is looking for 201300 instead of 201212

       

       

      =sum({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth ={$(=YearMonth-1)}>} Quantity)

      /

      sum({$<ActiveIngredient = P({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth ={$(=YearMonth-1)}>}),Year=,Month=,YearMonth ={$(=YearMonth-1)}>} Quantity)

        • Re: Yearmonth -1 Calculating as 201300 NOT 201212
          Stefan Wühl

          It's not a good idea to handle YearMonth as integer like 201301, as you already noticed, you'll run into trouble as year changes.

           

          Instead, use a QV date also for YearMonth, maybe created in your script like

           

          Date(monthstart(DATEFIELD),'YYYYMM') as YearMonth,

           

          then use addmonths() function to add/subtract any number of months needed.

           

          YearMonth = {$(=Date(addmonths(YearMonth,-1),'YYYYMM'))}

           

          Hope this helps,

          Stefan

            • Re: Yearmonth -1 Calculating as 201300 NOT 201212
              chris goudy

              when I use the expression below in a text box, I get 0.00% .

               

              =num(((sum({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth = {$(=Date(addmonths(YearMonth,-1),'YYYYMM'))}>} SEP)))

              , '#,##0.00%','.',',')

               

              I don't know if I did what you suggested correctly, but here is my Calendar Script below:

               

              //-------------------------------------------------------------------------------------------------------------------------

              // Calendar Details

              //-------------------------------------------------------------------------------------------------------------------------

               

              CalendarTemp:

              LOAD Distinct %_CalendarKey as YearMonth

              Resident Transactions;

               

              Calendar:

              left keep (Transactions)

              load 

                                  ,Date(monthstart(YearMonth),'YYYYMM') as YearMonth

                                  ,YearMonth AS %_CalendarKey   

                                  ,Year(DATE#(YearMonth,'YYYYMM')) as Year

                                  ,Month(DATE#(YearMonth,'YYYYMM')) as Month

                                  ,(Month(DATE#(YearMonth,'YYYYMM'))&Year(DATE#(YearMonth,'YYYYMM'))) as MonthYear

                                  //,Dual(Capitalize(Month(DATE#(YearMonth,'YYYYMM'))),Date(Monthstart(DATE#(YearMonth,'YYYYMM')), 'MM')) as Month

                                  ,'Q' & Ceil(Month(DATE#(YearMonth,'YYYYMM'))/3) as Quarter

                                  ,IF(Year(DATE#(YearMonth,'YYYYMM')) = '2009', 1, 0) as PPYFlag

                      ,IF(Year(DATE#(YearMonth,'YYYYMM')) = '2010', 1, 0) as PYFlag

                            ,IF(Year(DATE#(YearMonth,'YYYYMM')) = '2011', 1, 0) as CYFlag

                            ,num(Month(DATE#(YearMonth,'YYYYMM'))) as Period

              resident CalendarTemp;

               

              drop table CalendarTemp;

               

               

              ALSO, I have a bar chart with bars for each individual month going back 6 months. Will an expression like YearMonth = {$(=Date(addmonths(YearMonth,-1),'YYYYMM'))}  show a bar for each of those 6 months?

               

              right now I have an expression for each month, such as 1 month back, 2 months back, 3 months back:

               

              1 month back:

              =sum({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth1 ={$(=YearMonth-1)}>} SEP)

              /

              sum({$<ActiveIngredient = P({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth1 ={$(=YearMonth-1)}>}),Year=,Month=,YearMonth1 ={$(=YearMonth-1)}>} SEP)

               

              2 Months back:

              =sum({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth ={$(=YearMonth-2)}>} SEP)

              /

              sum({$<ActiveIngredient = P({$<ManufacturerGroup = {'A'},Year=,Month=,YearMonth ={$(=YearMonth-2)}>}),Year=,Month=,YearMonth ={$(=YearMonth-2)}>} SEP)



                • Re: Yearmonth -1 Calculating as 201300 NOT 201212
                  Stefan Wühl

                  If your original YEARMONTH is a string, you'll need date#() function to interpret it as a date:

                   

                     ,Date(monthstart(date#(YearMonth,'YYYYMM')),'YYYYMM') as YearMonth

                   

                   

                  If this worked out (check the content of the new field, it should be a date formatted as 'YYYYMM', but holding also a numeric value), then

                   

                  YearMonth = {">=$(=Date(addmonths(YearMonth,-5),'YYYYMM'))"} 

                   

                  should be the field modifier in a set expression to select the last 6 Months in field YearMonth (clear also the other date fields like you did before). This should work if one single YearMonth value is possible (e.g. selected).

                   

                  Hope this helps,

                  Stefan