Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Show last week values by each day separately

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). Qlikview Table Screenshot.png

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.

1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

Re: Show last week values by each day separately

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.

5 Replies
atkinsow
Valued Contributor II

Re: Show last week values by each day separately

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.

Not applicable

Re: Show last week values by each day separately

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?

atkinsow
Valued Contributor II

Re: Show last week values by each day separately

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.

Not applicable

Re: Show last week values by each day separately

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

atkinsow
Valued Contributor II

Re: Show last week values by each day separately

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

Community Browser