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?

       

      Cheers,

      Mike

       

      PS.

      I'm using the following script as master calendar.

      QuartersMap: 

      MAPPING LOAD  

      rowno() as Month, 

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

      AUTOGENERATE (12); 

        

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

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

       

      TempCalendar: 

      LOAD

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

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

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

       

       

       

       

      MasterCalendar: 

      Load 

                     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;