Report by Weekday with Totals

[#1]

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.

[Gysbert_Wassenaar · Accepted solution]

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

[Anonymous]

Hey!

This might help you! See the attachment.

[#4]

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

[Gysbert_Wassenaar]

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

[#6]

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.

[#7]

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.