Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Week to date in calendar

Hi Experts,

I have the below script for Master Calendar.

Let varMinDate = NUM(MAKEDATE($(vReviewPrevYear),1,1)); 

Let varMaxDate = NUM($(vToday)-1); 

     Set vCal_FD = 5; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

     Set vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

     Set vCal_RD = 4; // Reference day = This day in Jan defines week one {1..7}

TempCalendar: 

LOAD 

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

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

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

 

MasterCalendar:

  Load *, WeekYear & '-' & NUM(WeekNumber,'00') as YearWeek;

     Load *,

          Div( DECDDT - WeekStart( WeekYearRefDate, 0, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

          Year( WeekYearRefDate ) as WeekYear;

     Load *,

          Date( YearStart( If( $(vCal_BW), DECDDT, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;

     Load *,

          Date( WeekStart( DECDDT, 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;

Load 

               TempDate AS DECDDT,

               TempDate AS Date,

               NUM(TempDate) AS NumDate,

               WeekStart(TempDate, 0, $(vCal_FD) ) as WeekStart ,

               Year(TempDate) As Year, 

               Month(TempDate) As Month, 

               MonthName(TempDate) As MonthYear, 

            IF(TempDate<Today(),1,0) as  TD,

                Day(TempDate) As Day, 

               YeartoDate(TempDate)*-1 as CurYTDFlag, 

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

               If(InMonthToDate(TempDate, '$(vToday)'-1,0),1,0) as CurMTDFlag, 

              // If(InMonthToDate(DECDDT, '$(vToday)',0),1,0) as CurMTDFlag, 

              

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-1),0),1,0) as LastMTDFlag, 

               If(InMonthToDate(TempDate, ADDMONTHS('$(vToday)'-1,-2),0),1,0) as PrevMTDFlag, 

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

               WeekDay(TempDate) as WeekDay        

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar; 

I have calculated the month to date values using set analysis like

Sum({< MonthYear=, Date = {'>=$(=Date(MonthStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} SalesAmt)

Now i have to calculate(like above expre) for the current week i.e.,6/6/15 to 12/6/15 and for the previous week.

how can i do the same set analysis expression for week to date.

please guide me

1 Solution

Accepted Solutions
ramoncova06
Specialist III
Specialist III

in this case just tweek what Sunindia, already posted

=Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)0, 5 )<=$(=Date(vMaxSalesDate))'} >} SalesAmt)

View solution in original post

12 Replies
JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Hi PRRAJENDRAN :

Look this functions on the help file:

Weekyear()

Weekday()

Week()

Hope this helps you

Joaquín

sunny_talwar

Have you tried this:

Current Week

=Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} SalesAmt)

PreviousWeek

=Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate-7)))<=$(=Date(vMaxSalesDate-7))'} >} SalesAmt)

JoaquinLazaro
Partner - Specialist II
Partner - Specialist II

Hi again:

Perhaps you need this

LOAD ...

InWeek(MyDate,today(),-1) * (-1)  as %f_PreviousWeek,

InWeek(MyDate,today(),0) * (-1)  as %f_ThisWeek,

Sum({< %f_ThisWeek  = {1} } >} SalesAmt)

Sum({< %f_PreviousWeek = {1} } >} SalesAmt)

Joaquín

Not applicable
Author

Hi,

if i try this Sum({< MonthYear=, Date = {'>=$(=Date(WeekStart(vMaxSalesDate)))<=$(=Date(vMaxSalesDate))'} >} SalesAmt),

it returning data from 8/6/15 to 14/6/15 which is not correct.

In my calendar, the week starts on saturday and ends on Friday.


pls help me with the expression for this corresponding weekstart date

Not applicable
Author

Hi,

Its not working correct.

In my calendar, the week starts on saturday and ends on Friday.


pls help me with the expression for this corresponding weekstart date

Not applicable
Author

PLS GIVE SOME SUGESSTIONS.............

sunny_talwar

WeekStart() function allows for offsetting the days

Try this in a text box and see if you get desired dates

='>=' & Date(WeekStart(vMaxSalesDate, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, -2))

If it does than try this:


=Sum({<MonthYear=, Date = {"$(='>=' & Date(WeekStart(vMaxSalesDate, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, -2)))"}>} SalesAmt)

Not applicable
Author

No,

It is going two weeks back.

pls help

looking forward to ur reply

sunny_talwar

Sorry, I guess its the third parameter:

='>=' & Date(WeekStart(vMaxSalesDate, 0, -2)) & '<=' & Date(WeekEnd(vMaxSalesDate, 0, -2))