topic Re: Week to date in calendar in QlikView

Week to date in calendar

Tue, 09 Jun 2015 13:18:50 GMT

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

Re: Week to date in calendar

Joaquin_Lazaro — Tue, 09 Jun 2015 14:10:05 GMT

Hi PRRAJENDRAN :

Look this functions on the help file:

Weekyear()

Weekday()

Week()

Hope this helps you

Joaquín

Re: Week to date in calendar

sunny_talwar — Tue, 09 Jun 2015 14:15:16 GMT

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)

Re: Week to date in calendar

Joaquin_Lazaro — Tue, 09 Jun 2015 14:15:32 GMT

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

Re: Week to date in calendar

Tue, 09 Jun 2015 15:05:22 GMT

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

Re: Week to date in calendar

Tue, 09 Jun 2015 15:06:07 GMT

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

Re: Week to date in calendar

Tue, 09 Jun 2015 15:46:08 GMT

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

Re: Week to date in calendar

sunny_talwar — Tue, 09 Jun 2015 15:55:00 GMT

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)

Re: Week to date in calendar

Tue, 09 Jun 2015 16:04:51 GMT

No,

It is going two weeks back.

pls help

looking forward to ur reply

Re: Week to date in calendar

sunny_talwar — Tue, 09 Jun 2015 16:07:51 GMT

Sorry, I guess its the third parameter:

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

Re: Week to date in calendar

ramoncova06 — Tue, 09 Jun 2015 16:08:19 GMT

in this case just tweek what Sunindia, already posted

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

Re: Week to date in calendar

Tue, 09 Jun 2015 16:27:49 GMT

thanks ramon

Re: Week to date in calendar

Wed, 10 Jun 2015 09:33:12 GMT

Hi sunindia,

can u pls help me on the thread conditional values..Pls help ....itssssssssss urg...