topic Re: Report by Weekday with Totals in QlikView

Report by Weekday with Totals

Wed, 21 Oct 2015 15:10:34 GMT

I have a very specific requirement to create a report that looks like the following.

I have a data model with a field for each of the Time columns. The report should aggregate the data by the day of the week and display the average time for each record. So if one record has a date of Oct 12 and another has a date of Oct 19, both would be included in the data shown on the Monday line. The Today line would aggregate records with today's date only. The Overall Avg line would aggregate the entire data set. This table will be read-only but it should update based on selected criteria., ie, just data for October.

In order to associate a record with a specific day of the week, in my data model, I calculate the DayOfWeek field using the WeekDay() method which returns a dual with value of 0-6 (Mon-Sun).

Beyond that I have tried several things but always end up short of achieving my requirements. Can anyone give me some ideas on how to approach this? I will gladly provide more details if useful.

Re: Report by Weekday with Totals

Gysbert_Wassenaar — Fri, 23 Oct 2015 12:10:37 GMT

If you can calculate a weekday then you have a date field somewhere. I'm going to assume it's called MyDate. Ok, create a new table:

DaysOfTheWeek:

LOAD date(Today()) as MyDate, 'Today' as DayOfTheWeek

Autogenerate(1);

LOAD distinct MyDate, 'Overall avg.' as DayOfTheWeek

Resident Cycle;

LOAD distinct MyDate, Text(DayOfWeek) as DayOfTheWeek

Resident Cycle;

The DaysOfTheWeek is now linked with the Cycle table on the field MyDate.

You now have a new field DayOfTheWeek that you can use as dimension in your table. Your expressions would be like avg([Scope In Use Time])

Re: Report by Weekday with Totals

Anonymous — Fri, 23 Oct 2015 12:35:58 GMT

Hey!

This might help you! See the attachment.

Re: Report by Weekday with Totals

Fri, 23 Oct 2015 15:34:33 GMT

Thanks for the feedback. You got me really close to where I need to be. However, I still have an issue which I did not mention in my original posting.

1) If there is no data for a specific day of the week or today? The row falls off the table. I have forced all rows to be shown using "Show All Values" on the dimension and creating a dummy (Show) Expression to force a value. Not sure if this is the right/best approach.

2) Filtering & Sorting. I added a sort field to the DayOfTheWeek table which works when there is no selection filters. But when I add a selection filter, then I get the following.

Any ideas? Thanks for your help.

Fred

Re: Report by Weekday with Totals

Gysbert_Wassenaar — Fri, 23 Oct 2015 16:54:46 GMT

At least the sorting can be sorted out easily . Sort by an expression =only({1}DaySortOrder)

Re: Report by Weekday with Totals

Fri, 23 Oct 2015 17:12:18 GMT

Thanks so much. That did it. I had originally done all sorts of complicated and convoluted stuff to get it to work. Amazing what you can do when you have a better grasp of QlikView. Thanks again.

Re: Report by Weekday with Totals

Fri, 23 Oct 2015 17:37:39 GMT

Thanks for you reply and providing the sample document. I was able to get it to work the way that I wanted with Gysbert's similar approach.