topic Re: weekName as the x axis in QlikView

weekName as the x axis

josephinetedesc — Wed, 21 Jan 2015 09:17:37 GMT

Hi I would like the weekName as the x axis.

so if I have say 10 orders all placed on different weekNames

These 10 orders were activated on different weekNames

These 10 orders were delivered on different weekNames

so my

x axis should have as a Minimum value the minimum date of order placed.

x axis should have as a Maximum value the maximum date of delivery.

I want to show the activity per weekName - so for example on week x there were 3 orders placed, 2 orders were estimated to be delivered and 3 were delivered.

Thank you

Possible data

OrderNum	OrderDate	DateOfIssue	DateEstimatedDelivery	DateDelivered
1	1/01/2015	10/01/2015	24/01/2015	1/04/2015
2	2/01/2015	11/01/2015	24/01/1900	2/04/2015
3	3/01/2015	13/01/2015	24/01/2015	3/04/2015
4	10/01/2015	11/01/2015	24/01/2015	20/02/2015
5	11/01/2015	12/01/2015	24/02/2015	11/05/2015
6	12/01/2015	24/01/2015	24/02/2015	11/05/2015
7	13/01/2015	24/01/2015	24/02/2015	11/05/2015
8	13/01/2015	24/01/2015	24/02/2015	13/06/2015
9	13/01/2015	20/02/2015	1/04/2015	13/06/2015
10	20/02/2014	24/02/2015	1/04/2015	30/06/2015

Re: weekName as the x axis

Gysbert_Wassenaar — Wed, 21 Jan 2015 09:32:29 GMT

See attached qvw.

Re: weekName as the x axis

josephinetedesc — Wed, 21 Jan 2015 21:54:09 GMT

Thank you Gysbert,

count

({<DateType={'OrderDate'}>}OrderNum)

This translates to

Count the OrderNum, if the DataType is OrderDate ?

Thank you for your help - the other tihing is that not all orders are completed(ie have all 4 dates ...) so I will experiment to see the effect this has on the chart.

I would also like the x axis (weekNum dimension) to be continuous and display even if there is nothing happening for that week. Is this possible? I do have a date function script:

Thank you again. I would have answered last night, but my home QlkView has run out of new apps and I had to wait to come to work

//Load All Dates Between Years - Update For Dataset
LET vDateMin = Num(MakeDate(2014,07,01));
//LET vDateMax = Floor(today());
LET vDateMax = Num(MakeDate(2015,02,28));
//Update for Current Financial Year
SET vfinYear = '1416';
Let vLastfinYear = (Num(left($(vfinYear), 2))-1) & (Num(right($(vfinYear), 2))-1);

Fin_Dates_Temp:
LOAD * INLINE
[
MonthNm, FinMonthNm, Quarter,JoMonthNm
Jul, 01, 1,07
Aug, 02, 1,08
Sep, 03, 1,09
Oct, 04, 2,10
Nov, 05, 2,11
Dec, 06, 2,12
Jan, 07, 3,01
Feb, 08, 3,02
Mar, 09, 3,03
Apr, 10, 4,04
May, 11, 4,05
Jun, 12, 4,06
];

LET vDateToday = Num(Today());

//Temp Calendar
TempCalendar:
LOAD
Date($(vDateMin) + RowNo() - 1) as DateFull,
Month(Date($(vDateMin) + RowNo() - 1)) as Date_MonthNameTemp,
Num(Day(Date($(vDateMin) + RowNo() - 1))) as Date_DayNum,
WeekDay(Date($(vDateMin) + RowNo() - 1)) as Date_DayName,
Num(Month(Date($(vDateMin) + RowNo() - 1))) as Date_MonthNum,
Year(Date($(vDateMin) + RowNo() - 1)) as Date_YearNum,
WeekEnd(Date($(vDateMin) + RowNo() - 1)) as Date_WeekEnding

AUTOGENERATE 1
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);

//Join Financial Month Numbers
Left Join(TempCalendar)
LOAD
num(month(date#(MonthNm,'MMM'))) as Date_MonthNameTemp,
FinMonthNm as Date_FinancialMonthNum,
Quarter as Date_Quarter
Resident Fin_Dates_Temp;

Calendar:
LOAD *,
if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '-' & (Date_YearNum+1),
(Date_YearNum-1) & '-' & Date_YearNum) as Date_FinancialYearName,

if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_Fin,

if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Mid(Date_YearNum,3,2) & '/' & Mid((Date_YearNum+1),3,2),
Mid((Date_YearNum-1),3,2) & '/' & Mid(Date_YearNum,3,2)) as Date_FinancialYearShrt2,

'Q' & Date_Quarter & '_' & if(Date_FinancialMonthNum >0 and Date_FinancialMonthNum<=6,
Date_YearNum & '/' & Mid((Date_YearNum+1),3,2),
(Date_YearNum-1) & '/' & Mid(Date_YearNum,3,2)) as Date_QuartFinYear,

Dual(Date_MonthNameTemp, Date_FinancialMonthNum) as Date_Fin_Month
Resident TempCalendar;

DROP Tables

TempCalendar, Fin_Dates_Temp;

Re: weekName as the x axis

MarcoWedel — Wed, 21 Jan 2015 22:26:14 GMT

Hi,

you could create a canonical date calendar for this task.

Re: weekName as the x axis

josephinetedesc — Thu, 22 Jan 2015 00:17:29 GMT

I looked at Canonical Date ... it is a bit overwhelming. What I would like is something like: MinDate of x axis is the Minimum date of OrderDate, MaxDate of x axis is the Maximum date of " Estimated to be delivered Date"

Re: weekName as the x axis

Gysbert_Wassenaar — Thu, 22 Jan 2015 08:40:30 GMT

count ({<DateType={'OrderDate'}>}OrderNum)

This translates to Count the OrderNum, if the DataType is OrderDate ?

Yes, that's correct.

I would also like the x axis (weekNum dimension) to be continuous and display even if there is nothing happening for that week. Is this possible? I do have a date function script:

You can set the axis to continuous. If your weekNum field is a date field (for example created using the weekstart function) you can set the number format for the continuous axis to Date on the Numbers tab and enter a format string. Unfortunately there's no format string to display the week number.

Re: weekName as the x axis

josephinetedesc — Mon, 26 Jan 2015 23:55:39 GMT

Thank you the "continous" was exactly what i needed.

I am not sure how to do the helfpful/correct answer ... but for me it was both helpful and correct ..