Yearmonth -1 Calculating as 201300 NOT 201212

[Anonymous]

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)

[swuehl]

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

[Anonymous]

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)

[swuehl]

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