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

[sinanozdemir]

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)

[Anonymous]

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)

[sinanozdemir]

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

[paul_scotchford]

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

[Anonymous]

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?

[Anonymous]

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).

[paul_scotchford]

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.

[Anonymous]

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?

[paul_scotchford]

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)