5 days a week

[#1]

How can i count with 5 days in a week. I want only count with the workdays (5) in the week. Exemple: when it is sunday and i want to a total of one day ago so i will see the total of fryday. 2 days ago than it is the total of thursday. If it is Wednesday i will see Monday and  Tuesday. My definitie is now

sum(if ([CapDatumAchterstand] < ( vToday ),[CapInUrenAchterstand]))

and

sum(if ([CapDatumAchterstand] < ( vToday--1),[CapInUrenAchterstand]))

[swuehl]

Not sure if I understood correctly, because I think your above definitions will sum all CapInUrenAchterstand values before vToday resp. vToday-1.

If you want to sum the total of one workday ago, I would try something like:

=sum({<Date= {"$(=firstworkdate(today(),1))"}>} Value)

This will some Values of last friday. I used a set expression, but I think this should be equivalent:

=sum(if(Date = firstworkdate(today(),1), Value) )

If you want to sum the last two workdays, and it will not harm your results if you just include the non-working days, you could use something like

=sum({<Date= {">=$(=firstworkdate(today(),2))<=$(=firstworkday(today(),1))"}>} Value)

or

=sum(if(Date >= firstworkdate(today(),2) and Date <= firstworkdate(today(),1), Value))

You could use vToday (or $(vToday) ) instead of today() function, and you could also define your hoilday in the firstworkday function to exclude them also.

Hope this helps,

Stefan

[#3]

Stefan,

Tanks a lot for your answer.

But i want count all hours in the past till one day or two days before Today without take into the calculation the saturday and sunday.

My week must have only five workdays.

Greetings

Gerrit

-

---

swuehl <qliktech@sgaur.hosted.jivesoftware.com> schreef:

swuehl created the discussion

"Re: 5 days a week"

To view the discussion, visit: http://community.qlik.com/message/166618#166618

[swuehl]

I believe you could add a check for weekday (Mon=0 to Sun=6) then:

=sum({<Date= {"=weekday(Date)<5 and Date < firstworkdate(today(),2) "}>} Value)

or

=sum(if( weekday(Date)<5 and Date < firstworkdate(today(),2), Value))

to sum all workdays value up to 2 workdays (excluding) prior today.

Alternatively, you could add a workday flag to the data model (use if(weekday(Date)<5,1,0) as WorkDay), then could use something like

sum( Value*WorkDay )

to sum all values only on workdays.

Hope this helps,

Stefan

edit: Since we are already limiting the weekdays, you maybe don't need the firstworkdate() anymore or a simple today()-2 might also do. I admit I haven't fully understood your full requirement at this point.

[#5]

Stefan,

Thanks, i will try tomorrow something with Weekday.

I will let know you if the problem is solved.

Maybe you have idea about this problem can solved in the

guide card "settings".

Kind regards,

Gerrit

The Netherlands

-

---

swuehl <qliktech@sgaur.hosted.jivesoftware.com> schreef:

swuehl created the discussion

"Re: 5 days a week"

To view the discussion, visit: http://community.qlik.com/message/166625#166625

[swuehl]

Do you mean the settings menu within the QV application, like document settings? I don't think that there is a global setting to only consider weekdays in calculation, or do I misunderstand something?

[#7]

Stefan,

I mean where you can type things like this:

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='$#,##0,00;($#,##0,00)';

SET TimeFormat='h:mm:ss TT';

SET DateFormat='DD/MM/YYYY';

SET TimestampFormat='DD/MM/YYYY';

SET MonthNames='Jan;Feb;Mrt;Apr;Mei;Jun;Jul;Aug;Sep;Okt;Nov;Dec';

SET DayNames='Maa;Din;Woe;Don;Vry;Zat;Zon';

Let vYearAdj = Year(Today()) - 2010;

LET numMonth = 12 * $(vYearAdj);

ODBC CONNECT TO VRE;

Kind regards

Gerrit

[swuehl]

Hi Gerrit,

so you are talking about the script, right?

I am not sure how familiar you are with the back end / script part of QV, but I think you could either create an additional field (maybe called IsWorkDay) like mentioned above, in the same table as your Date field:

Load

...

if(weekday(Date)<5,1,0) as IsWorkDay,

...

from ...

Another way would be to use variables to abstract the Workday part of your expression, and you can define these variables either in the script (using LET) or in the variable overview dialog in the front end. But first check that you get your results using the above expressions or after your modifications.

Regards,

Stefan

[#9]

Stefan,

With this solution i can select workdays en no workdays.

But the database have only workdaydates.

So, if i will count the arrears of hours over a weekend then the sunday en the saterday is

also count. I will create a week of five days.

Kind regards

Gerrit

-

---

swuehl <qliktech@sgaur.hosted.jivesoftware.com> schreef:

swuehl created the discussion

"Re: 5 days a week"

To view the discussion, visit: http://community.qlik.com/message/166740#166740

[swuehl]

Gerrit,

not sure if I fully understand. You have a solution now somehow or do you still have a problem?

Maybe if you could upload a small sample, this will help me or someone else to understand and then help you with a solution. You can upload files in the advanced editor.

Regards,

Stefan