Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
If you are a Qlik Insight Bot user, join this collaborative group: JOIN GROUP
Not applicable

Report by Weekday with Totals

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

Avg Scope Procedure & Reprocessing Times.png

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.

DataModel.png

Tags (2)
1 Solution

Accepted Solutions
Highlighted
MVP & Luminary
MVP & Luminary

Re: Report by Weekday with Totals

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


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Highlighted
MVP & Luminary
MVP & Luminary

Re: Report by Weekday with Totals

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


talk is cheap, supply exceeds demand

View solution in original post

Highlighted
Contributor III

Re: Report by Weekday with Totals

Hey!

This might help you! See the attachment.

Highlighted
Not applicable

Re: Report by Weekday with Totals

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.

No filters.png

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.

Sorting.png

Any ideas?  Thanks for your help.

Fred

Highlighted
MVP & Luminary
MVP & Luminary

Re: Report by Weekday with Totals

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


talk is cheap, supply exceeds demand
Highlighted
Not applicable

Re: Report by Weekday with Totals

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.

Highlighted
Not applicable

Re: Report by Weekday with Totals

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.