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

[Anonymous]

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

[justinvchiang]

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)