Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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.
Perhaps with and expression like this: rangeavg(above(sum(scrap),0, rowno()))
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
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.
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.
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?
I have tried to attached again.
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)
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
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.