topic Re: Creating YTD average starting from 01/01/2016 in QlikView

Creating YTD average starting from 01/01/2016

Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Tue, 13 Dec 2016 12:49:54 GMT

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

Re: Creating YTD average starting from 01/01/2016

Wed, 14 Dec 2016 06:34:24 GMT

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

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Wed, 14 Dec 2016 07:17:23 GMT

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.

Re: Creating YTD average starting from 01/01/2016

Wed, 14 Dec 2016 07:28:30 GMT

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.

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Wed, 14 Dec 2016 07:39:49 GMT

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?

Re: Creating YTD average starting from 01/01/2016

Wed, 14 Dec 2016 07:46:18 GMT

I have tried to attached again.

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Wed, 14 Dec 2016 08:28:57 GMT

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)

Re: Creating YTD average starting from 01/01/2016

Wed, 14 Dec 2016 09:01:44 GMT

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

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Wed, 14 Dec 2016 09:26:01 GMT

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.

Re: Creating YTD average starting from 01/01/2016

Wed, 14 Dec 2016 10:53:53 GMT

Sweet as. Thanks for your help.

Any suggestions for learning Qlikview?

All the best

Terry

Re: Creating YTD average starting from 01/01/2016

Gysbert_Wassenaar — Wed, 14 Dec 2016 11:06:43 GMT

See How to become a QlikView Developer?‌

There are more good books that you could read to learn more: Books and literature