4 Replies Latest reply: Oct 9, 2017 3:40 AM by Mike Slottje RSS

    Previous quarter - set analysis expression

    Mike Slottje

      Hi experts,


      In a dashboard, I want to show our sales in the previous quarter for current year with the following expression:

      Sum({<Date={">=$(=QuarterStart(Today())-1) <=$(=QuarterEnd(Today())-1)"}>}[Nett Amount new EUR])

      Unfortunately this expression doesn't work.

      I tried this expression, because this works perfectly for the previous month when using the same expression with MonthStart en MontEnd instead.


      Could you guys help me out to find an expression that works for the previous quarter?






      I'm using the following script as master calendar.



      rowno() as Month, 

      'Q' & Ceil (rowno()/3) as Quarter 

      AUTOGENERATE (12); 


      LET varMinDate = num('1-1-2009');

      LET varMaxDate = num('31-12-2018');   




      $(varMinDate)+Iterno()-1 AS Num,

      Date(($(varMinDate))+Iterno()-1) AS TempDate

      AUTOGENERATE 1 WHILE $(varMinDate)+Iterno()-1<= $(varMaxDate);







                     floor(date(TempDate,'DD-MM-YYYY')) AS %key_Date,

                     TempDate AS Date,

                     week(TempDate) As Week, 

                     Year(TempDate) As Year, 

                     Month(TempDate) As Month, 

                     Day(TempDate) As Day, 

                     YeartoDate(TempDate)*-1 as CurYTDFlag, 

                     YeartoDate(TempDate,-1)*-1 as LastYTDFlag, 

                     inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12, 

                     date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

                     date(monthstart(TempDate), 'YYYY-MMM') as YearMonth,

                     ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

                     Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

                     WeekDay(TempDate) as WeekDay,

                     DayNumberOfYear(TempDate) as DayoftheYear,

                     If( DayNumberOfYear(TempDate) <= DayNumberOfYear(Today()), 'Y', 'N' ) as IsInYTD,

                     If( DayNumberOfQuarter(TempDate) <= DayNumberOfQuarter(Today()), 'Y', 'N') as IsInQTD,

             If( Day(TempDate) <= Day(Today()), 'Y', 'N') as IsInMTD,

             If( Month(TempDate) = Month(Today()), 'Y', 'N') as IsCurrentMonth,

             If( Month(AddMonths(TempDate,1)) = Month(Today()), 'Y', 'N') as IsLastMonth

      Resident TempCalendar 

      Order By TempDate ASC;