YTD Calculation

[Anil_Babu_Samineni]

Dear Friends,

I want to calculate YTD value?

How, Can anyone help me please

I tried this

=sum({$<Year=,Month=,C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

If i am using this, It is working. But, while i trying to create whole year and whole month it is not working

=sum({$<Year={2016},Month={1},C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

[settu_periasamy]

Hi Anil,

Just to confirm, how your Month filed looks?

did you try to remove the Month field from the set expression, is that worked?

[ziadm]

Hi

The best practice is to create a Master Calendar Table having all your Dimension such as YTD, MTD, WTD

and the use YTD flag in your set analysis to get your metrics

you may use the following  code

MinMax:

LOAD

  Min(DATEEDIT) as MinDate,

  Max(DATEEDIT) as MaxDate

RESIDENT SALESTable;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

//LET vToday = Num(Today(0));

//

///*************** Temporary Calendar *************

//Generates a single table with one field containing

//all existing dates between MinDate and MaxDate.

//*/

////

//

//

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS TempDate

AUTOGENERATE

  $(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

MasterCalendar:

LOAD

  TempDate AS CalendarDate,

  Week(TempDate) AS Week,

  Year(TempDate) AS Year,

  Month(TempDate) AS Month,

  Day(TempDate) AS Day,

  Weekday(TempDate) AS WeekDay,

  'Q' & ceil(month(TempDate) / 3) AS Quarter,

  //Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

  Week(TempDate)&'-'&Year(TempDate) AS WeekYear,

    inmonthtodate(TempDate, $(vToday), 0) * -1 AS MTDFlag,

  inyeartodate(TempDate, $(vToday), 0) * -1 AS CurYTDFlag,

    inmonthtodate(TempDate, addyears($(vToday),-1), 0) * -1 AS LastYMTDFlag,

    inyeartodate(TempDate, addyears($(vToday),-1), 0) * -1 AS LastYTDFlag

RESIDENT TempCal

ORDER BY TempDate ASC;

DROP TABLE TempCal;

[Anil_Babu_Samineni]

My Month and Year fields looks like

Date Field - S_CREATED_DATE,

year(S_CREATED_DATE) as Year,

num(Month(S_CREATED_DATE)) as Month,

And after your reply i use this expression

=sum({<C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

I filter for 2016 and 4th Month

The image looks like this. Why Month and YTD values are same because we are using same expression for Month and YTD. But, here i want to calculate YTD, How, I don't know?

[Anil_Babu_Samineni]

Do we required Master Calendar to create the YTD for this situation. If i created same way then how to calculate YTD?

Help Me!!

[maxgro]

one solution is to add a flag field (see Ziad Mohammad 's answer or look at the InYearToDate function) to your date, year, etc...... fields

InYearToDate(YourDateField, today(), 0) as CurYTDFlag

and use that field to filter

=sum({$<CurYTDFlag={1}, Year=,Month=,C_DESCRIPTION={'Reportable','Recordable','Restricted work Cases','Lost Work Cases'},P_Des-={'All Plants'}>} DISTINCT S_COUNT)

another solution, without adding fields to the script is to filter the date with set sanalysis (add the other fields to the expression)

sum({$ <Month=,Year=,YourDateField={"<=$(=today())"}>} val)

or

sum({$ <Month=,Year=,YourDateField={"<=$(=date(today(), 'DD/MM/YYYY'))"}>} val)

replace DD/MM/YYYY with your date format

[Anil_Babu_Samineni]

This is My Calendar, My Values are not coming

SafetyCounter_dbo_3BSafety:

S_CREATED_DATE

//Fresh Calendar

MinMax:

LOAD

  Min(S_CREATED_DATE) as MinDate,

  Max(S_CREATED_DATE) as MaxDate

RESIDENT SafetyCounter_dbo_3BSafety;

LET vMinDate = Num(Peek('MinDate', 0, 'MinMax'));

LET vMaxDate = Num(Peek('MaxDate', 0, 'MinMax'));

LET vToday = $(vMaxDate);

TempCal:

LOAD

  date($(vMinDate) + rowno() - 1) AS S_CREATED_DATE

AUTOGENERATE

  $(vMaxDate) - $(vMinDate);

DROP TABLE MinMax;

/*************** Master Calendar ***************

Disconnected during the Date Island exercise by renaming TempDate as IslandDate

*/

MasterCalendar:

LOAD

  S_CREATED_DATE AS CalendarDate,

  Week(S_CREATED_DATE) AS Week,

  Year(S_CREATED_DATE) AS Year,

  Num(Month(S_CREATED_DATE)) AS Month,

  Day(S_CREATED_DATE) AS Day,

  Weekday(S_CREATED_DATE) AS WeekDay,

  'Q' & ceil(month(S_CREATED_DATE) / 3) AS Quarter,

  //Date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,

  Week(S_CREATED_DATE)&'-'&Year(S_CREATED_DATE) AS WeekYear,

    inmonthtodate(S_CREATED_DATE, $(vToday), 0) * -1 AS MTDFlag,

  inyeartodate(S_CREATED_DATE, $(vToday), 0) * -1 AS CurYTDFlag,

    inmonthtodate(S_CREATED_DATE, addyears($(vToday),-1), 0) * -1 AS LastYMTDFlag,

    inyeartodate(S_CREATED_DATE, addyears($(vToday),-1), 0) * -1 AS LastYTDFlag

RESIDENT TempCal;

//ORDER BY TempDate ASC;

DROP TABLE TempCal;

[#8]

Give 2016 & 1 in single quotes.