Last year to "todays" date

[#1]

I have a column called Gross Sales Values - Last Year To Date

My selection is:

Year: 2013.

Month: jan, feb, mar, apr, may (i.e I can select 1 or many months but in this ex I have selected all from jan to may)

If I make above selection today  (24/05/13) I would like to see the sales between 01/01/12 and24/05/12 but I cannot figure out how to write the set analysis statement.

e.g.

sum({$<UCYEA4 = {$(=Year(MakeDate(UCYEA4)))}>} amount) => will today (24/05/13) give me sales between 01/01/13 and 24/05/13

sum({$<UCYEA4 = {$(=Year(MakeDate(UCYEA4)-1))}>} amount) => will today (24/05/13) give me sales between 01/01/12 and 31/05/12 because last year may has already been a full month.

I assume I need to add a "todaysdate" somehow using the "calendermonthname" (format "jan") ?

Can you help me please. 🙂

[Sokkorn]

Hi,

Sound you like to calculate YTD. Is it correct? BTW, UCYEA4 is a date field (24/05/13)? Do you have any calendar table?

Here is some clue for YTD

Sum({<DateField = {">=$(=YearStart(DateField,-1))<=$(=AddMonths(Max(DateField),-12))"} >} Sales)

Regards,

Sokkorn

[#3]

Sorry UCYEA4 = 2013 and yes, I have a calendar table.

I  want to calculate Last YTD, ie the purpose is to compare sales

1/1/12-24/5/12 to 1/1/13-24/5/13 using selection field ucyea4 (2013) and calendarmonthname (jan-may)

[Sokkorn]

Hi,

Can you replace [DateField] to your date field in my sample set analysis and see the resule?

Regards,

Sokkorn

[er_mohit]

Try this

sum({$<Date = {">=$(=MonthStart(Max(Date),-12))<=$(=MonthEnd(Max(Date)))"},Year=,Month=>}Sale)

[#6]

Sorry did not work as my field is not a DATE it is a YEAR.

The user has 2 selections boxes; Year and Month

Field for Year is UCYEA4 and represents 2011, 2012, 2013 etc

Field for Month is CalendarMonthName and represents Jan, Feb, Mars, Apr, May etc through to Dec.

If I select  2013 May I expect to see a sum of sales for

* This Year to Date: Between 01/05/13 and 27/05/13 (i.e. the date and time it was last reloaded)

* Last Year to Date: Between 01/05/12 and 27/05/12 (i.e. same date and time as this year but for last year)

However, I can only manage to get Last Year to Date to sum the whole month for last year, i.e. 01/05/12 - 31/04/12 and that's not what I want.

Maybe I need to change the "format" for year and calendarmonthname..

[#7]

Sorry, did not work. I am not using a DATE field only YEAR (2012, 2013 etc) and CalendarMonthName (Jan, Feb etc)

How do I convert these 2 into 1 date field which stops at Max(date)?

[Sokkorn]

Hi khagglund,

Do you have date field represent data like 27/05/13? We should use date to check condition while your target is YTD and LYTD.

Regards,

Sokkorn

[#9]

The transactions contain a year field (2012) and a date filed (e.g. 20120527). Does not contain a month.

I have in a calendar table created Apr, May etc from the date field above.

The users do not want to select a date they want to select a Year and a Month. (2012, May etc) 

Is this to hard?

[Sokkorn]

Hi khagglund,

Sure, use no need to select date (20120527). In your case, we need to calculate YTD. And to get this job done, we need one hidden field for date (20130527). How many column in your calendar table?

Regards,

Sokkorn