3 Replies Latest reply: Sep 11, 2017 1:47 AM by Momin Tahemas RSS

    Set analysis using week number of date dimension

    Michael Cox

      Looking for help from the community:


      I have a simple table that looks something like this:


      Date  Type Value

      09/09/2017  S  100

      10/09/2017  S  100

      I am trying to get a set analysis work that sums Values but only where the week number of the Date is equal to the current week number (in future I may change this to be some other variable).

      What I've tried:


      Created a variable to get current week number: 

      vCurWeek = Week([Date], 0)

      Used the following measure in my table: 

      Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)

      But get no results.

      I've tested the variable as well as using Week([Date], 0) in a text box/table and both are ok. I just can't get it to work as part of the set analysis, despite it looking pretty much the same as the correct answer in this post: Help to SET Analysis - get past 4 weeks

      Thank you!

        • Re: Set analysis using week number of date dimension
          Devarasu R

          Try like this,



          load *,Week(Date) as Week;

          load * Inline [

          Date,  Type, Value

          09/09/2017,  S,  100

          10/09/2017,  S,  100



          create variable as




          Set analysis expression as

          =Sum({$< Week = {$(vCurWeek)} >} Value)


          P.S: best way u can use master calendar script

          • Re: Set analysis using week number of date dimension
            VIJAY VIRA



            You will not be able to use like you have it. The highlighted portion in red has to be replaced by a weeknumber field that you may have calculated in your script.


            Sum({$< Week([Date],0) = {$(vCurWeek)} >} Value)

            In the post you have included it is a field comparison to a variable

            Count({<weeknumber = {$(vCurrentWeek)}>} solditems).


            Providing a larger sample data and your script may help us to help you.


            Best Regards,


            • Re: Set analysis using week number of date dimension
              Momin Tahemas


              LOAD * INLINE


              minDate,  Type, Value

              09/09/2017,  S,  100

              10/09/2017,  S,  100




              MAPPING LOAD  

              rowno() as Month,

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

                AUTOGENERATE (12);  




              min(date(minDate,'DD-MM-YYYY'),) as minDate,     

              max(Today()) as maxDate 

              Resident mstDate; 

              drop table mstDate; 

              Let varMinDate = Num(Peek('minDate', 0, 'Temp')); 

              Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

              Let vCurWeek   = Num(Peek('maxDate', 0, 'Temp'));

              DROP Table Temp;   




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

              Date($(varMinDate) + IterNo() - 1) as TempDate,

              Date($(CurWeek) + IterNo()-1) as WeekDate     

              AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);               




              TempDate AS  Date,             

                num( TempDate) AS Datenum,               

                week(TempDate) As Week,                

                Year(TempDate) As Year,                

                Month(TempDate)As Month,  

                Week(WeekDate) as CurWeek,             

                'Q' & Ceil (Month(TempDate)/3) as Quarter1 ,             

                 num(Month(TempDate)) As MonthNum,              

                 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,

                   num(date(monthstart(TempDate), 'MMM-YYYY')) as MonthYearNum, 

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

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

                   WeekDay(TempDate) as WeekDay 

                   Resident TempCalendar 

                   Order By TempDate ASC; 

                   Drop Table TempCalendar;


              Set analysis expression as

              =Sum({$< Week = {$(vCurWeek)} >} Value)