10 Replies Latest reply: Jan 26, 2016 7:10 PM by Sangram Reddy

# Set Analysis for MTD, YTD, Week, Today

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

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Edit: I edited my original reply so take a look at the below one.

Hi,

It seems like you are missing "=" sign:

Sum({\$<Year={\$(vYearPrevious)},Month={\$(vMonthPrevious)},ActivityDate={"=today()"}>}Net_Unit_Gain)

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Hi Sinan,

I tried both, but no luck. It showed dash in the KPI.

Sum({\$<Year={\$(vYearPrevious)},Month={\$(vMonthPrevious)},ActivityDate={"=today()"}>}Net_Unit_Gain)

Sum({\$<Year={\$(vYearPrevious)},Month={\$(vMonthPrevious),ActivityDate={'1/24/2016'}>}Net_Unit_Gain)

• ###### Re: Set Analysis for MTD, YTD, Week, Today

What is the format of ActivityDate field, date-time? If so, you may need to re-format it.

• ###### Re: Set Analysis for MTD, YTD, Week, Today

I reformatted  date(floor(RepDate),'MM/DD/YYYY') AS ActivityDate in the data load,

1. Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={'01/24/2016'}>}Net_Unit_Gain). This works

2. Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={max(ActivityDate)}>}Net_Unit_Gain). Not work.

3. Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={today()-1}>}Net_Unit_Gain). Not Work.

If I always need the max date, How could I resolve the 2 and 3, please? Should I format the date in the set analysis?

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Incidentally, Max date will only return a max date in the context of your current year/month predicate, hence if no activity in the predicated dates then no value will be returned.

The correct format for your max date is this ... ActivityDate={"=max(ActivityDate)"}

and for using today                                this ... ActivityDate={"=today()-1"}

Try that see how you go.

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Hi Paul,

I tried below two, but it returned the entire month of Jan 2016 value, from 1/1-1/24/2016. The largest day is 1/24/2016 in my table.

1. Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={"=max(ActivityDate)"}>}Net_Unit_Gain)

2. Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={"=today()-1"}>}Net_Unit_Gain)

What should I do to make returned value on 1/24/2016 only?

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Hi Bill

Sorry in my haste , I did not notice that you had not wrapped the \$ expansion in quotes ... the below will work ...

1. Sum({\$<Year={"\$(vYearCurrent)"},Month={"\$(vMonthCurrent)"},ActivityDate={"=max(ActivityDate)"}>}Net_Unit_Gain)

2. Sum({\$<Year={"\$(vYearCurrent)"},Month={"\$(vMonthCurrent)"},ActivityDate={"=today()-1"}>}Net_Unit_Gain)

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Just wondering why you are testing today but the other date modifiers are testing Last Year / Last month ? No result

will be returned from your calc!

cheers

Paul

• ###### Re: Set Analysis for MTD, YTD, Week, Today

Yes. I noticed that problem.

It should be below, however, it returned dash in the KPI

Sum({\$<Year={\$(vYearCurrent)},Month={\$(vMonthCurrent)},ActivityDate={max(ActivityDate)}>}Net_Unit_Gain).

• ###### Re: Set Analysis for MTD, YTD, Week, Today

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