topic 5 days a week in QlikView

5 days a week

Sun, 20 Nov 2011 15:17:43 GMT

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

5 days a week

swuehl — Sun, 20 Nov 2011 16:10:01 GMT

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)

=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

Re: 5 days a week

Sun, 20 Nov 2011 16:58:27 GMT

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

Re: 5 days a week

swuehl — Sun, 20 Nov 2011 17:26:46 GMT

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)

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

Re: 5 days a week

Sun, 20 Nov 2011 19:37:03 GMT

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

5 days a week

swuehl — Mon, 21 Nov 2011 00:08:02 GMT

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?

5 days a week

Mon, 21 Nov 2011 06:56:00 GMT

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

5 days a week

swuehl — Mon, 21 Nov 2011 11:28:42 GMT

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

Re: 5 days a week

Mon, 21 Nov 2011 19:22:05 GMT

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

5 days a week

swuehl — Mon, 21 Nov 2011 20:04:41 GMT

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

Re: 5 days a week

Tue, 22 Nov 2011 13:21:03 GMT

Stefan,

Here are my QV application.

Gerrit

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

swuehl swuehl created the discussion

"Re: 5 days a week"

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

5 days a week

swuehl — Tue, 22 Nov 2011 13:43:54 GMT

Gerrit,

your app has an OnOpen event that reloads, which of course does not work at my site, so I don't see any data.

Could you please deactivate the reload and exchange the app?

Re: 5 days a week

Tue, 22 Nov 2011 14:19:56 GMT

Stefan,

Now without reloading.

Gerrit

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

swuehl swuehl created the discussion

"Re: 5 days a week"

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

5 days a week

swuehl — Tue, 22 Nov 2011 14:32:00 GMT

Ok, what would be the expected results then for today?

Re: 5 days a week

Tue, 22 Nov 2011 14:35:51 GMT

Today result:

1 workday in the past: 01-01-2011 till 21-11-2011

2 workdays in the past: 01-01-2011 till 18-11-2011

Gerrit

5 days a week

swuehl — Tue, 22 Nov 2011 14:51:25 GMT

Ok, I undestand, but the actual expected numbers?

Using

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

for 2 workdays in the past (I noticed I need to subtract 1day for actually getting the correct results, because FirstWorkDate will also take the enddate into account).

I get:

Kstplts	Per Dag	Gevraagde Capaciteit	Cap. per aant. Dgn	In %	Achterstand t/m 1 dag terug	Achterstand t/m 2 dagen terug	sum(if ( CapDatumAchterstand < FirstWorkDate( vToday-1,2),[CapInUrenAchterstand]))
105	339	199	339	59%	948	708	501,2
110	107	187	107	174%	1899	1788	1666,51
115	60	85	60	142%	36	20	6,88
120	58	38	58	67%	88	78	66,08
125	45	62	45	139%	173	162	144,18
130	38	2	38	6%	12	12	11
150	38	5	38	13%	161	160	159

Re: 5 days a week

Tue, 22 Nov 2011 14:59:36 GMT

Stefan,

What do you mean with: but the actual expected numbers?

Kind regard,

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/167164#167164

Re: 5 days a week

swuehl — Tue, 22 Nov 2011 15:22:19 GMT

Well, I mean the numbers you expect to show up in your table, or do I misunderstand something completely?

Anyway, what about the numbers I posted above (most right column)?

Re: 5 days a week

Tue, 22 Nov 2011 15:45:15 GMT

Stefan,

Thanks a lot. The numbers are correct.

Kinf 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/167191#167191