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

# Yearmonth -1 Calculating as 201300 NOT 201212

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

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,

Hope this helps,

Stefan

• ###### Re: Yearmonth -1 Calculating as 201300 NOT 201212

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:

Resident Transactions;

Calendar:

left keep (Transactions)

,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

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