4 Replies Latest reply: Jan 19, 2017 9:10 AM by jeanneteau stephane RSS

    WeekName format with set analysis formula

    jeanneteau stephane

      Hi,

       

      I loaded data :

      LOAD [Case Number],

           Type_date,

           date,

           Hour(date) as Hour_date,

           date(date) as Date_value,

            Day(date) AS  calendar_date, 

           WeekDay(date) AS  weekday, 

           Week(date) AS  week, 

           Month(date) AS  Month, 

           'Q' & Ceil(Month(date)/3) AS  quarter, 

           Year(date) AS  year, 

           // Calendar Date Names 

           WeekName(date) as  week_year, 

           MonthName(date) as  month_year, 

           QuarterName(date) as  quarter_year

      FROM

      [Dates.qvd]

      (qvd);

       

      I created variables like this, with the field called 'date' :

      let vMaxYearWeek  = '=WeekName(max(date))';

      let vPriorYearWeek  = '=vMaxYearWeek-1';

      let vMaxWeek  = '=week(max(date))';

      let vPriorWeek  = '=vMaxWeek-1';

       

      Then i used this in a table :

      ScreenHunter_69 Jan. 19 11.35.gif

      Foir 'cteated last week' column :

      =count(DISTINCT {$ < week= {$(=vPriorWeek)},  Type_date = {"Open"}> }  [Case Number] )

      And it works well, but I realized it wasn't the right format date as i could have data from the same week but different year. I have to use year-week.

       

      But if i use this one, I have got '0' value :

      =count(DISTINCT {$ < week_year= {$(=vPriorYearWeek)},  Type_date = {"Open"}> }  [Case Number] )

       

      I checked results with text object like this :

      =vMaxWeek : result is 3

      =vPriorWeek : result is 2

      =vMaxYearWeek   result is 2017/03

      =vPriorYearWeek result is 42750

      So i change the last case like this :

      =WeekName(vPriorYearWeek) and the result is 2017/02. What I wanted

       

      So if i change table formula with weekname format, it doesn't works.

      =count(DISTINCT {$ < week_year= WeekName({$(=vPriorYearWeek)}),  Type_date = {"Open"}> }  [Case Number] )

      after weekname, bracket is underlined by red

      ScreenHunter_68 Jan. 19 11.33.gif

       

      Could you help me please?

      What have I to do in order to change the format of the variable matching 2017/02 and not 42750 with analysis formula?

      PS : i use ScreenHunter_70 Jan. 19 11.41.gif

      Thanks

       

      Stephane