5 Replies Latest reply: May 12, 2017 6:15 PM by Wallo Atkinson RSS

    Show last week values by each day separately

    Anna Tsardoulias

      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.