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

    Set Analysis for MTD, YTD, Week, Today

    bill yu

      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
          Sinan Ozdemir

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




          It seems like you are missing "=" sign:



          • Re: Set Analysis for MTD, YTD, Week, Today
            Sangram Reddy

            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.


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