How to exclude weekends from expression

[#1]

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

[MK_QSL]

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))

[its_anandrjs]

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)

[Anonymous]

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

[Michiel_QV_Fan]

"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.

[#6]

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

[SunilChauhan]

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