YTD and MTD Expression

[abhaysingh]

I want to show YTD and MTD , value of sales. please help me in the expression

[#2]

Hi Abhay,

Its always better to use YTD & MTD in your master calendar.
In the expression level, you can use MonthStart() & YearStart() function in your expression.

--Lalit

[its_anandrjs]

For the expressions YTD and MTD you have a Datefield and then check the YTD and MTD expression with that  but need to check you have latest dates in the database then write expression like below explanation. Also take any date dimensions for the charts.

YTD Expression

=Sum({$<Datefield = {'>$(=Max((YearStart(Today())))) <=$(=Max(Today()))'}>} Sales)

Or

=Sum({$<Datefield = {'>$(=Max((YearStart(Datefield)))) <=$(=Max(Today()))'}>} Sales)

MTD Expression

=Sum({$<Datefield = {'>=$(=Max((MonthStart(Today())))) <=$(=Max(Today()))'}>} Sales)

Or

=Sum({$<Datefield = {'>=$(=Max((MonthStart(Datefield)))) <=$(=Max(Today()))'}>} Sales)

[abhaysingh]

Hi lalit

thanks for ur input but could u pls elaborate your ans with some example.

Regards

abhay

[MK_QSL]

Rather than using Set Analysis, you can do the same by creating Flags in the script itself...

===============

Let vCompareDate = Today();

Sales:

Load * Inline

[

  Date, Sales

  01/01/2014, 100

  01/02/2014, 120

  01/03/2014, 1200

  05/04/2014, 180

  10/05/2014, 1000

  01/06/2014, 150

  05/06/2014, 1200

];

Load

  Date,

  If(InMonthToDate(Date, '$(vCompareDate)',0),'MTD') as Flag

Resident Sales;

Concatenate

Load Date,

  If(InYearToDate(Date,'$(vCompareDate)',0),'YTD') as Flag

Resident Sales;

=====================

Now create a list box for Flag

and

use the same for you selection...

Or you can use something like below

=SUM({<Flag = {'YTD'}>}Sales)

or

=SUM({<Flag = {'MTD'}>}Sales)

REFERENCE :

Valerio Fatatis