YTQ, QTD, MTD and WTD

[CELAMBARASAN]

Year, Quarter, Month and Week To Date are the common analysis that I seen many applications. I will share the expression to do here

First to do this your data model should have the DateField in number format by applying floor

Similar to this

Floor(DateField) AS DateNum //it will gives you one whole number to represent date

YTD - Year To Date

A date should be selected and it will look for the Starting date of the year to the selected date.

Ex: date selected is 21-03-2014 then YTD is 01-01-2014 to 21-03-2014

Expression would be

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(YearStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

QTD- Quarter to Date

In the place of year use Quarter

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(QuarterStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

MTD- Month to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(MonthStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

WTD- Week to Date

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num(WeekStart(Max(DateNum))))<=$(=Max(DateNum))"}>} Sales)

if you want you can set a variable value as 'Year', 'Month', 'Quarter', 'Week', lets say vToDate and go with single chart and single expression

Sum({<Year=, Month=, Quarter=, Week=, DateField=, DateNum={">=$(=Num($(=vToDate& 'Start(Max(DateNum))')))<=$(=Max(DateNum))"}>} Sales)

Will keep your expression simple

Regards,

Celambarasan

[vinieme12]

What if there is No Need for Set Analysis with dates?

Period Presets: Compare Periods on the fly

Your Expressions will simply be

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

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

and so on.

[roberto_musitel]

Hi All,

I've just joined the community and trying to use my first YTD dymension.

i've followed all your suggestions but still have problems (probably dued to the fields format?)

this is the autocalendar in my datamodel

[autoCalendar]:

  DECLARE FIELD DEFINITION Tagged ('$date')

  FIELDS

  Dual(Year($1), YearStart($1)) AS [Year] Tagged ('$axis', '$year'),

  Dual('Q'&Num(Ceil(Num(Month($1))/3)),Num(Ceil(NUM(Month($1))/3),00)) AS [Quarter] Tagged ('$quarter'),

  Dual(Year($1)&'-Q'&Num(Ceil(Num(Month($1))/3)),QuarterStart($1)) AS [YearQuarter] Tagged ('$axis', '$yearquarter'),

  Month($1) AS [Month] Tagged ('$month'),

  Dual(Year($1)&'-'&Month($1), monthstart($1)) AS [YearMonth] Tagged ('$axis', '$yearmonth'),

  Dual('W'&Num(Week($1),00), Num(Week($1),00)) AS [Week] Tagged ('$weeknumber'),

  Date(Floor($1)) AS [Date] Tagged ('$date');

DERIVE FIELDS FROM FIELDS [Data] USING [autoCalendar] ;

and this is the expression I'm using:

Sum({<Year=, Month=, Quarter=, Week=, Date={">=$(=(YearStart(Max(Date))))<=$(=Date(Max(Date)))"}>}  Sales)

thanks in advance

Roberto

[Karim_Khan]

can we ve the data sample

[Karim_Khan]

Thanks it worth a lot for Fresher and experienced both

[roberto_musitel]

Ciao Karim

This is the datasample

[oknotsen]

If you are going to reply to a post by email, please remove all unnecessary stuff (like your complete signature and the whole "if you are not the right person" text) before pressing send. I have edited your post and removed 90% of the text.

Also note that when replying to a topic via email, you can not attach anything. For that you need to login to the community and make the post yourself.

[Anonymous]

Very helpful read! Thanks

[avicentbarcelo]

Very good job.

TX's

[#10]

Hi, how do you get the previous YTD when the date field is not in a number format?

[Karim_Khan]

share the Date format