two different kind of periods

[#1]

Hello,

Can anyone tell me what the syntax is to compare 2 different kind of periods? Thereby i want to have the choice to choose a specific date (e.g. 10-07-2012) or a month or a year or a period of days (e.g. 01-07-2012 to 08-07-2012).

This then should be compared with last year, so:

10-07-2012 vs 10-07-2011

or

month vs month last year

or

(this) year vs last year

or

01-07-2012 to 08-07-2012 vs 01-07-2011 to 08-07-2011

Something tells me this must be a very simple formula but i cannot put my finger on the right one.

Thanks!

[Anonymous]

It is simple.  Use functions InMonthToDate() and InYearToDate() in conditions.

(Sorry, have to run now, will be back tomorrow if help is still needed by that time)

[Anonymous]

OK here it is.

Assuming you need sum(Sales), and there is SalesDate field.  Your specific date (base date  10-07-2012) - let use a variable for it, call it BaseDate.  So, the calcualtions will be:
MTD:  sum(if(InMonthToDate, SalesDate, BaseDate, 0), Sales)
MTD last year: sum(if(InMonthToDate, SalesDate, BaseDate, -12), Sales)
Last MTD:  sum(if(InMonthToDate, SalesDate, BaseDate, -1), Sales)
YTD:  sum(if(InYearToDate, SalesDate, BaseDate, 0), Sales)
Last YTD:  sum(if(InYearToDate, SalesDate, BaseDate, -1), Sales)

Now you can easy compare the parts you want.  for example, difference between sales MTD and last year MTD:
sum(if(InMonthToDate, SalesDate, BaseDate, 0), Sales) - sum(if(InMonthToDate, SalesDate, BaseDate, -12), Sales)

And you can change Base Date if needed.

Regards,
Michael

[#4]

Hello Michael,

Thank you for your help.

I’ve looked at your formulas but these are all fixed periods. My wish is to select any random period. Is that possible?

Met vriendelijke groet,

Rene Santifort

Drogisterij.net, veilig en vertrouwd

Voor 21:00 uur besteld, morgen in huis

[Anonymous]

Rene, could you please provide an example of "random period"?

[#6]

Say, i want to compare 1-7-2012 with 1-7-2011

and on another day a want to compare 4-3-2011 to 4-6-2011 with 4-3-2010 to 4-6-2010

So every date range i choose (this could be a day, a date range, a week, a month, a year) should be choosable and be compared with the year before.

Hopefully this clarifies things.

Regards,

Rene Santifort

Drogisterij.net, veilig en vertrouwd

Voor 21:00 uur besteld, morgen in huis

[Anonymous]

It is a little different, and a little more complex, but not impossible.  Following is an example with set analysis where you get the previous year data by selecting current year dates (separate days, or months, etc.)

For the current year it is simple:
sum(Sales)

To get previous year, same days, create a variable, let's call it DateList, defined like this:
=chr(39) & concat(addyears(SalesDate,-1), chr(39) & ',' & chr(39)) & chr(39)

It contains comma-separated list of dates one year back.  If you select SalesDate = 07/15/2012, the variable creates string '07/15/2011'.  If you select multiple dates (directly or by selecting months), variable contains all these dates.

And, the expression to calculate last year data is:
sum({<Month=, Year=, SalesDate={$(DateList)}>} Sales)

If you need something like this for the previous month instead of the previous year, the variable is a little different:
=chr(39) & concat(addmonths(SalesDate,-1), chr(39) & ',' & chr(39)) & chr(39)

Hope this is what you need.

Regards,
Michael