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.

           

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

            Example:

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



            Thanks,

            Sangram