Creating YTD average starting from 01/01/2016

[#1]

I have data of scrap as a percentage (x kilos of scrap/ z kilos of product) for each week of 2016. I have created a bar chart to display this data. I would like to add a line for YTD average. So for week 1 it will equal week 1, for week 2 it will be (week 1 + week 2)/2,

then (week 1+week2+week3)/3, and so on. How can I do this?

[Gysbert_Wassenaar]

Perhaps with and expression like this: rangeavg(above(sum(scrap),0, rowno()))

[#3]

Thanks for the suggestion Gysbert. I notice you answer a lot of questions on Qlik community. I am going to assume you are quite knowledgeable of its use. I, on the other hand, am a newbie. Sorry if I ask stupid questions.

My field for the scrap if titled "weight". I have created a combo bar/line graph. I have "week" as my dimension, and sum(weight) as my first expression, and I am trying to display the YTD average with my second expression (line). I have tried your suggestion as: rangeavg(above(sum(weight),0,rowno())).

This did not produce any line on the graph. Should I be inputting something in the parenthesis after "rowno"?

I would include my Qlikview file so you could see it but have not figured out how to do this yet. I am using the free personal version of Qlikview so maybe it is not an option for me.

I have entered some code for Mastercalendar and then for a As-of Calendar (code I found on forum from HIC). Could I accomplish my goal using this method?

QuartersMap: 

MAPPING LOAD  

rowno() as Month, 

'Q' & Ceil (rowno()/3) as Quarter 

AUTOGENERATE (12); 

Temp: 

Load 

  min(C) as minDate, 

  max(C) as maxDate 

Resident InjectionScrap; 

Let varMinDate = Num(MakeDate(2015,12,30)); 

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp')); 

DROP Table Temp; 

TempCalendar: 

LOAD 

  $(varMinDate) + Iterno()-1 As Num, 

  Date($(varMinDate) + IterNo() - 1) as TempDate 

  AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

MasterCalendar: 

Load

  TempDate As C, 

  week(TempDate) As Week, 

  Year(TempDate) As Year, 

  Month(TempDate) As Month,   

  Day(TempDate) As Day, 

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter, 

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear, 

  WeekDay(TempDate) as WeekDay,

     Date(MonthStart(TempDate), 'YYYY MMM') as Month1

Resident TempCalendar 

Order By TempDate ASC; 

Drop Table TempCalendar;

tmpAsOfCalendar:

Load  distinct Month1

  Resident MasterCalendar ;

// ======== Cartesian product with itself ========

Join (tmpAsOfCalendar)

Load Month1 as AsOfMonth

  Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========

[As-Of Calendar]:

Load Month1,

  AsOfMonth,

  Round((AsOfMonth-Month1)*12/365.2425) as MonthDiff,

  Year(AsOfMonth)-Year(Month1) as YearDiff

  Resident tmpAsOfCalendar

      Where AsOfMonth >= Month1;

Drop Table tmpAsOfCalendar;

Thanks again,

Terry

[Gysbert_Wassenaar · Accepted solution]

This did not produce any line on the graph. Should I be inputting something in the parenthesis after "rowno"?

Probably not. It depends on how many dimensions are involved. Feel free to check the help file: RowNo ‒ QlikView

I would include my Qlikview file so you could see it but have not figured out how to do this yet.

See this document: Uploading a Sample‌. Even if you won't be able to open the document after I modify it, it will at least allow us to see what the data model is and what you're trying to do.

I have entered some code for Mastercalendar and then for a As-of Calendar (code I found on forum from HIC). Could I accomplish my goal using this method?

Yes, an AsOf table sounds like a good solution for this. But the script for the As-Of table you posted is based on months, not weeks. You'd have to change that so it uses weeks as unit instead of months. Or perhaps days is even better. Also I don't see any relation with any date or week field from the rest of your data model.

In fact let's use the Qlik Components library to create the AsOf table. Download the qvc-11.1.zip‌ and extract it. Then look in the Examples directory and open AsOfTable.qvw example. At least I hope those still can be opened by QV PE as well. You'll need three lines to create an AsOf table:

$(Must_Include=..\qvc_runtime\qvc.qvs);

CALL Qvc.AsOfTable('YourDateFieldHere');

CALL Qvc.Cleanup;

Make sure to change the path to the qvc.qvs file so it points to where you installed it on your file system and replace YourDateFieldHere with the field name of the date field in your model. If you only have a week and a year field then first create a date field using the MakeWeekDate() function.

[#5]

I have attached my Qlikview file for your reference. This is as it was when I asked the question. I have not tried changing it with your suggestions yet.

[Gysbert_Wassenaar]

I don't see an attachment yet. It's possibly a small bug in the jive software that sometimes keeps attachments hidden. Can you edit your post again and remove and re-add it?

[#7]

I have tried to attached again.

[Gysbert_Wassenaar]

Ok, create your WeekYear like this in your master calendar:

Dual(Week(TempDate)  & '-' & WeekYear(TempDate) , Weekstart(TempDate)) as WeekYear,

Then replace the last part of your script with this:

// ======== Cartesian product with itself ========

Join (tmpAsOfCalendar)

Load DISTINCT WeekYear as AsOfWeek

  Resident tmpAsOfCalendar ;

// ======== Reload, filter and calculate additional fields ========

[As-Of Calendar]:

Load WeekYear,

  AsOfWeek,

  Floor((AsOfWeek-WeekYear)/7) as WeekDiff,

  Round((AsOfWeek-WeekYear)*12/365.2425) as MonthDiff,

  Year(AsOfWeek)-Year(WeekYear) as YearDiff

  Resident tmpAsOfCalendar

      Where AsOfWeek >= WeekYear;

Drop Table tmpAsOfCalendar;

And then use AsOfWeek as dimension in your chart and as expression sum({$<YearDiff={0}>} Weight) / count(distinct {$<YearDiff={0}>} WeekYear)

[#9]

Hi Gysbert,

I have done as you suggested. Some success. However my bar chart is not correct now. Not sure if the issue is with the expression or something else.

Any advice?

Thanks

[Gysbert_Wassenaar]

You probably want the first expression to be sum({$<WeekDiff={0}>} Weight) so it shows the sum of Weight of that week only, not the YTD average.