YTD Calc !!!

[#1]

Hi

Just a question if I do the following it works, it's a year to date calc

sum( {$<Period ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

But now when I want to do the following it doesn't work (Have a look at the period)

sum( {$<YR*100+MTH ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

That is the calc to get to my period, its in the back end, but when I do it in the front end, it incorrect, I think its got something to do with the Syntax I think it needs quotes etc like

sum( {$<=$("YR*100+MTH") ={">=$(=date(yearstart(today(),-1),'YYYYMM')) <=$(=date(addmonths(today(),-13),'YYYYMM'))"}, [Product Manufacturer] = {'Aspen-Pharmacare'} >} Sales )

Please assist!

Thanks in advance J

[sparur]

Hello,

What is YR*100+MTH ? It's fieldName? As I know, in set Analysis you can use FieldName only.

[#3]

YR is a Field Name as well as the MTH

They look like this YR = 2009

MTH = 1

So YR*100+MTH = 200901 That creates a "period" Or YearMonth...

[Miguel_Angel_Baeyens]

Hello,

Although I would create that field in the master calendar so you can reference it as "period" or "year-month" field (or dimension if any), you can check the year and month (that you already have as YR and MTH) from the date field.

Regards.

[#5]

Correct, that is the way of doing it on the most correct way i suppose, but if can have the syntax to do this in the front end, i can use it on string values etc. To not pass a Field but a calculation in set analysis.

Read My first post,

Thanks,

[Miguel_Angel_Baeyens]

Hi,

Say you don't have a "Year" field but you can check for a given year in your Set Analysis. This is one way to do it:

{< DateField = {"=Year(DateField) = '2009'"} >}

The first part of the set condition must be always a field name. So you will need to check first for year (2009), then for month (1), to get the period you want

{< DateField = {"=Year(DateField) = '2009'"}, DateField = {"=Month(DateField) = 'jan'"} >}

but I understand that you already have year and month field, although not in 'YYYYMM' format, and that's why you I'd rather separate both.