Show last week values by each day separately

[#1]

I'm relatively new to both Qlikview and SQL, so please bear with me if this is an easy answer. I did not create the document I am trying to edit, but I have been tasked with the future growth and maintenance of it.

I currently have a table with individuals and how many messages they've sent out weekly. However, I want to extend the table to also show how many messages have been sent out each individual day of the previous week (Sun-Sat).

The expression for the total messages for the week is sum({<week = {'$(=max(week))'}>}[MESSAGE SOURCE1])+sum({<week = {'$(=max(week))'}>}[MESSAGE SOURCE2])

I have in my script: SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';

and

LOAD date(date, 'M/D/YYYY') as date,

     if(weekyear(week)<weekyear(today()), date(week, 'M/D/YYYY') ,if(week(week) < week(today()), date(week, 'M/D/YYYY'))) as week,

     date(month, 'M/D/YYYY') as month,

     quarter,

     fiscal_year;

LOAD date,

     week,

     month,

     quarter,

     fiscal_year

already, but I'm not sure if that is helpful for this question or not.

How do I call out each day individually without selecting them manually? I need it to be the previous week as this report is run every Tuesday automatically, so while the last Wednesday-Saturday are the most recent of those, I need the last Sunday-Tuesday.

Any and all help will be much appreciated. If there's any further information I need to provide, please let me know.

[Anonymous · Accepted solution]

Add weekday name to your script as a field.

weekday(date) as WeekdayName

If you make your object a pivot table it will then be easy.

Add WeekDayName as a dimension in your pivot table and drag it to the top header row.

Your expression can stay the same.

[#3]

Hi Wallo,

That did get the days of the weeks in there as columns. Thank you so much!

Just to ask, is there any way to have a total for the week at the beginning or end of the columns? Or will I have to keep both tables separate and just add them both to the report?

[Anonymous]

Go to the Presentation tab and click  'Partial Sum' on weekday dimension.

Selecting Subtotals at Top or Bottom will determine whether the total is at the beginning or end.

[#5]

I am so glad this was more simple than I was trying to make it. Thank you again for your help.

[Anonymous]

You're welcome.  I'm glad you got what you needed.