topic Re: Show last week values by each day separately in QlikView

Show last week values by each day separately

Fri, 12 May 2017 18:16:08 GMT

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.

Re: Show last week values by each day separately

Anonymous — Fri, 12 May 2017 20:48:04 GMT

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.

Re: Show last week values by each day separately

Fri, 12 May 2017 21:08:18 GMT

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?

Re: Show last week values by each day separately

Anonymous — Fri, 12 May 2017 21:11:42 GMT

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.

Re: Show last week values by each day separately

Fri, 12 May 2017 21:52:41 GMT

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

Re: Show last week values by each day separately

Anonymous — Fri, 12 May 2017 22:15:15 GMT

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