topic Re: How to exclude weekends from expression in QlikView

How to exclude weekends from expression

Tue, 15 Apr 2014 09:44:32 GMT

Hi All,

I have an expression where i want to exclued the weekend date.

Eg: I am have a field as Date and variable vMaxDatee = max(Date) = 07-Apr-2014 and

Variable = vLastMonth = max(Date)-30 = 08-Mar-2014.

08-Mar-2014 is a saturady so my Variable should get Date as 07-Mar-2014.

My Current expression is

Sum(if(Period = $(vMaxDate)-30 and Period = $(VMaxDate) and File_Type = 'Container',Metric_Value))

I want to give $(vMaxDate)-30 value if comes to day as saturady or sunday it should provide me date of Friday.

Can anybody please help its urgent.

Thanks in Advance.

Regardrs,

Pranav

Re: How to exclude weekends from expression

MK_QSL — Tue, 15 Apr 2014 09:51:44 GMT

May be like this?

Sum(if(Period =IF(WeekDay($(vMaxDate)-30)='Sat',Date($(vMaxDate)-31),

IF(WeekDay($(vMaxDate)-30)='Sun',Date($(vMaxDate)-32),Date($(vMaxDate))))

and Period = $(VMaxDate) and File_Type = 'Container',Metric_Value))

Re: How to exclude weekends from expression

its_anandrjs — Tue, 15 Apr 2014 09:53:08 GMT

Update please check now

If in load script you add another field

=WeekDay( Period ) then you get Sun,Mon,Tue,Wed,Thu,Fri,Sat then in expression use like

Try like

Sum( {< Period = {'Mon','Tue','Wed','Thu','Fri'}, File_Type = { 'Container' } >} Metric_Value)

Re: How to exclude weekends from expression

Anonymous — Tue, 15 Apr 2014 09:55:24 GMT

Use

vLastMonth=firstworkdate(max(Date)-30),1)

it gives you the last workday before your "Date", so instead of saturday you get friday

Hope it helps

Re: How to exclude weekends from expression

Michiel_QV_Fan — Tue, 15 Apr 2014 10:01:22 GMT

"I want to give $(vMaxDate)-30 value if comes to day as saturady or sunday it should provide me date of Friday."

In your load add to your master calendar:

if(weekday(yourdate) > 5, 4, weekday(yourdate)) as workingdays.

This will give you for saturday (5) and sunday (6) the friday (4) or else 0 to 3 for mon to thu. This way the values from sat and sun will be counted on friday, if that is what you need.

Add workingdays to your chart as dimension.

Please keep in mind (quote help text):

If the date format used does not correspond to the one set in your operating system, QlikView will not be able to make a correct interpretation.

Re: How to exclude weekends from expression

Wed, 16 Apr 2014 08:07:05 GMT

Hi Rudolf,

Thanks for your reply.

I tried your suggestion and placed the Variable inside my expression but then also it didnt worked.

Let me give you my original expression for your refrence.

Please let me know if any changes are required in the same.

My Original expression:

sum({<AsOFPeriod = {'>$(=date($(vMaxDate)-30)) <=$(=date($(vMaxDate)))'},

Last ={'last_30'},File_Type = { 'Container' }, Auto_Number={'1'} >} Metric_Value) /1000

If i try to create a variable as vLastMonth =firstworkdate(max(Date)-30),1) or any other formula nd place it in expression it is not working.

Could you please help.

Regards,

Pranav

Re: How to exclude weekends from expression

SunilChauhan — Wed, 16 Apr 2014 08:24:51 GMT

Tab1:

Load * Inline[

DayExcludedweekends

Mon

Tues

Wed

Thus

Fri

];

Left join(Tab1)

Load*,

Weeday(Date) as Day

from path

and use what you are using in variable and expression

hope this helps