1 Reply Latest reply: Dec 13, 2017 5:54 PM by Justin Chiang RSS

    Add in Year-to-Date or average column in bar chart

    Polly Stewart

      Hi,

       

      I have really struggled to find an answer to this on the qlikview community as I currently only have the Personal edition and cannot open files to look at what people did - any answers please screenshot/type what you did as I won't be able to see the answer otherwise!

       

      I want to add a YTD column in my bar chart of Visits per Day (VPD). This would be the average of all the months up until the current Month ( Month < Month(Today())  or something like this), where for each month and for the YTD there is: a box plot for the objective VPD, a symbol for the previous year VPD and a symbol for the actual VPD this year.

       

      Also, I would like a column that shows the forecasted total year VPD, (that also shows the total  year objective and previous year).

       

       

      If this doesn't make sense, happy to answer more questions but I have attached a qlikview file with example data.

       

      As I mentioned initially, please can you describe/screenshot/type example expressions here as I will not be able to open a new version of the document

       

      Thanks in advance!

      Polly

        • Re: Add in Year-to-Date or average column in bar chart
          Justin Chiang

          Do you have a master calendar in your data set?

           

          if not, that's step 1, as that will allow you to use the various date flags in your set expressions.  (The below will work but you may need to change DATE_KEY to whatever date attribute you have in your data set)

           

          //New Calendar
          LET vMinDate = NUM(Date#('2017-01-01', 'YYYY-MM-DD')); //2017 and onward
          LET vMaxDate = NUM(Today());
          LET vToday = $(vMaxDate);

          //********************* Temporary Calendar ***********************//
          tmp_CALENDAR:
          LOAD
          Date($(vMinDate) + RowNo() - 1) AS tmp_Date
          AUTOGENERATE
          $(vMaxDate) - $(vMinDate) + 1;

          //********************* Master Calendar ***********************//

          MASTER_CALENDAR:
          LOAD
          tmp_Date AS DATE_KEY, //link to your primary date
          Week(tmp_Date) AS Week, //a week is Monday through Sunday
          Year(tmp_Date) AS Year,
          Month(tmp_Date) AS Month,
          'Q' &
          ceil(Month(tmp_Date)/3) AS [Reg Quarter], //regular calendar quarters, Q1=Jan-March, Q2=April-June, ...
          Day(tmp_Date) AS Day,
          WeekDay(tmp_Date) AS WeekDay,
          Date(monthstart(tmp_Date), 'MMM-YYYY') AS MonthYear,
          Year(tmp_Date) & '-' & num(Month(tmp_Date), '00') AS YearMonth, //2017-01
          WeekYear(tmp_Date) & '-' & num(Week(tmp_Date), '00')  AS YearWeek, //year-week, 2017-38
          if(Year(tmp_Date)=WeekYear(tmp_Date), Week(tmp_Date) - Week(MonthStart(tmp_Date)) + 1,  Week(tmp_Date) + 52 - Week(MonthStart(tmp_Date)) + 1) AS [Week in Month]//week number within a month, 1, 2, 3, 4, 5, 6
          InYearToDate(tmp_Date, $(vToday), 0) * -1 AS CurYTDFlag,
          InYearToDate(tmp_Date, $(vToday), -1) * -1 AS LastYTDFlag,
          InMonthToDate(tmp_Date, $(vToday), 0) * -1 AS CurMTDFlag,
          InMonthToDate(tmp_Date, $(vToday), -1) * -1 AS LastMTDFlag,
          InWeekToDate(tmp_Date, $(vToday), 0) * -1 AS CurWTDFlag,
          InWeekToDate(tmp_Date, $(vToday), -1) * -1 AS LastWTDFlag
          RESIDENT tmp_CALENDAR
          ORDER BY tmp_Date ASC;

           

          After you have the date flags (CurYTDFlag etc.) you can use them in your VPD expressions e.g. avg(VPD * CurYTDFlag)