topic Sum hours for a week in QlikView

Sum hours for a week

Mon, 09 Jul 2012 15:29:49 GMT

How do I set up a bar chart to only sum my 'Timereported' field for a week at a time? Then change when the new week begins.

Re: Sum hours for a week

swuehl — Mon, 09 Jul 2012 16:05:46 GMT

Not sure if I've understood correctly:

a) if you want to group by week, over all weeks, create a week field in your data model. I assume you already have a date field DATE:

LOAD

DATE,

VALUE,

week(DATE) as WEEK // or weekname(DATE) as WEEK

from TABLE;

Then just use sum(VALUE) as expression in a bar chart with dimension WEEK.

b) If you only want to see the current week:

=sum({<DATE = {">=$(=weekstart(today()))<=$(=today())"}>} VALUE)

as expression and no dimension or DATE as dimension (one total bar or a bar per day).

Re: Sum hours for a week

Mon, 09 Jul 2012 17:13:14 GMT

My current dimension are my groups ('UserGroup') because I'm looking at the amount of hours recorded for each group.

I currently have two dimensions in the bar chart...

sum

(TimeReportedActual)

count(distinct(TechID)) * 40

I'm using the first one to sum up the time reported and then using the second one to calculate (based on the number of people in each group) how many hours should ACTUALLY be recoreded each week (based on each person doing a 40 hour work week) so that way both bars for each group should be equal every week if each group is putting in all their time.

I did go and create the week variable with 'week(DATE) as week'. So I need a way for the first equation to show only the current weeks hours that were recorded.

I hope this helps describe what I'm looking to do.

Re: Sum hours for a week

Mon, 09 Jul 2012 18:04:49 GMT

Also, I want this to always stay with the current week and only for the total hours/reported hours like I said above, regardless of what the users picks, ie the 3rd quarter or the month of June. It would still display information about the current week.

I tried to use that equation above

=sum({<DATE = {">=$(=weekstart(today()))<=$(=today())"}>} VALUE)

But it shows everything from Date onward as a field (its shown in red).

Re: Sum hours for a week

Mon, 09 Jul 2012 18:32:08 GMT

I'm sorry I misspoke. I want it to only show the previous week.

Re: Sum hours for a week

Tue, 10 Jul 2012 12:27:03 GMT

Could you explain further? You always have the right answers!

I think I'm just using that equation wrong somehow.

Re: Sum hours for a week

swuehl — Tue, 10 Jul 2012 12:35:38 GMT

It might be a problem with date formatting. Could you upload a small sample file?

In general, your bar chart expressions should look like

=sum({<Date = {">=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))"}>} TimeReportedActual)

resp.

=count({<Date = {">=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))"}>} distinct TechID)*40

where Date is the name of your Date field, with Date format being your standard date format.

Re: Sum hours for a week

Tue, 10 Jul 2012 13:03:34 GMT

I attached the qvw. The Dashboard is what has the bar graph.

If it could, I want a bar graph to show last weeks hours and then another to show the current weeks hours.

Re: Sum hours for a week

swuehl — Tue, 10 Jul 2012 13:13:13 GMT

I do get a bar chart like this:

What's wrong with this?

This chart should show the values for last week (Haven't checked in detail).

If you want to get a chart that is insensitive to selections in calendar fields, you might need to clear your calendar fields in the set expression:

=count({<Date = {">=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))"}, Month=, Day=, Year=, Week=, Quarter=>} distinct TechID)*40

Re: Sum hours for a week

Tue, 10 Jul 2012 13:19:54 GMT

I get that as well. I guess the numbers there are right. Is there a way to make it show last weeks numbers regardless of the user selections?

Re: Sum hours for a week

swuehl — Tue, 10 Jul 2012 13:28:15 GMT

Try a set identifier 1 in both set expressions, like

=count({ 1 <Date = {">=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))"} >} distinct TechID)*40

Re: Sum hours for a week

Tue, 10 Jul 2012 13:39:01 GMT

That works to keep the selection regardless of user selection.

Thanks for all your help. You're very helpful as always!

Re: Sum hours for a week

Wed, 11 Jul 2012 17:19:32 GMT

Having a slight problem with the bar graph now.

I attached the .qvw again with the problem.

The bar chart should only be displaying 'target hours' for 'user group' that have distinct 'TechID's' and multiply by 40 hours.

So why does my bar chart have 'PS' labeled as 40 when if you go over to the Details tab, there are no 'PS' value in the UserGroup field?

Re: Sum hours for a week

swuehl — Wed, 11 Jul 2012 18:48:51 GMT

Some of your Dates in table TimeLog2 are in fact timestamps and don't link to your calendar.

Use

daystart(TimeLogDate) as Date,

to create a date without a time part (maybe add a date() around for formatting).

Regards,

Stefan

Re: Sum hours for a week

Wed, 11 Jul 2012 19:38:31 GMT

This would be in the script?

Re: Sum hours for a week

swuehl — Wed, 11 Jul 2012 20:17:37 GMT

Yes, do this in the load script (as a hint, as can only be used in the script). Just replace your TimeLog2 Load line

TimeLogDate as Date,

with

daystart(TimeLogDate) as Date,

date(daystart(TimeLogDate)) as Date,

Re: Sum hours for a week

Wed, 11 Jul 2012 20:33:27 GMT

That did not work. The 'PS' field is still showing up in the bar graph.

As an aside, you wouldn't happen to be able to help explain section access/ have a sample qvw that uses it?

Re: Sum hours for a week

swuehl — Wed, 11 Jul 2012 20:37:58 GMT

Yeah, the PS field will probably still show up, but you see the details for the July 5th now, don't you?

Then please attach an updated file.

There is quite a nice document explaining section access in the docs section:

http://community.qlik.com/docs/DOC-1853

Re: Sum hours for a week

Wed, 11 Jul 2012 20:43:11 GMT

Aha! It seems to keep showing up because theres a value I had hidden on the details tab for July 5th that is null. How can I prevent it from being in the bar graph?

Re: Sum hours for a week

swuehl — Wed, 11 Jul 2012 21:18:51 GMT

Not sure what your criterium to hide the record is, a NULL in TimeReportedActual for that date? You can consider only records that show a value in that field using something like

=count({<TimeReportedActual = {"*"}, Date = {">=$(=weekstart(today(),-1))<=$(=weekend(today(),-1))"},

Month=, Day=, Year=, Week=, Quarter=>} distinct(TechID))*40