Set Analysis for MTD, YTD, Week, Today

[Anonymous]

Hi, I am having difficulties to create set Analysis for MTD, YTD, Week, Today. Although I found some posts online, Set Analysis for certain Point in Time, it is not working for me.

So thur, I have created below variables and renamed field. 

LET vYearCurrent = Year(Today());

LET vMonthCurrent = Month(Today());

Year has been renamed as Year, Month renamed as Month,  date(floor(RepDate),'MM/DD/YYYY') AS ActivityDate,

It works: Sum({$<Year={$(vYearCurrent)},Month={$(vMonthCurrent)}>}Net_Unit_Gain)

It doesn't work: Sum({$<Year={$(vYearPrevious)},Month={$(vMonthPrevious)},ActivityDate{$(today())}>}Net_Unit_Gain).

I want to create set analysis that always show today or yesterday sale result, year to date, month to date, and week to date. How may I able to do so? Appreciate that

[reddy-s]

Hi Bill,

I am assuming that you have a master calendar already:

Once you have that:

make use of the following expressions which will flag the respective values:

inYeartoDate(DateField,today(),0) * (-1) as YTD // 1 for year to date

inYeartoDate(DateField,today(),-1) * (-1) as LYTD// last year to date

inMonthtoDate(DateField,today(),0) * (-1) as MTD // month to date

inMonthtoDate(DateField,today(),-1) * (-1) as LMTD // lastmonth to date

inWeek(DateField,today(),0) * (-1) as thisWeek // this week

as you now have all the flagged values, you can easily find the sum of the period needed.

Example:

Sum({$< YTD = {1} >}Net_Unit_Gain)

Thanks,

Sangram