20 Replies Latest reply: Jul 12, 2012 8:18 AM by Ethan Beaulieu

# Sum hours for a week

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

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:

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

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

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

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

• ###### Re: Sum hours for a week

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

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

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

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

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

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

That works to keep the selection regardless of user selection.

• ###### Re: Sum hours for a week

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

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

This would be in the script?

• ###### Re: Sum hours for a week

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,

or

date(daystart(TimeLogDate)) as Date,

• ###### Re: Sum hours for a week

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

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

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

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

• ###### Re: Sum hours for a week

Thats great. Works perfect now. I don't want to show the null values put in for time.

I appreciate all your help, as usual!!