topic Last Year To Date in QlikView

Last Year To Date

kaygee28 — Fri, 29 Jul 2016 07:21:40 GMT

Giood

I know this question might've been asked a million times before but nothing seems to help I want to create a rolling 12 month chart but what I want to do first is to autonumber my date entries sequentially i.e 2134 is assigned to 12 Dec 2012 and 2135 is assigned to 13 Dec 2013, but it seems like its a mission at the moment cause this preceding load currently doesn't assign the ID in a sequential mode.

Load

AutoNumber([Day Of Order]) as [MonthID];

here is my complete code below:

let vBudget2008=300000;

let vBudget2009=500000;

let vBudget2010=600000;

let vBudget2011=800000;

let vBudget2012=400000;

let vBudget2013=600000;

let vBudget2014=700000;

let vBudget2015=800000;

let vBudget2016=800000;

//Preceding load in order to use derived fields [Actaual Delivery Date] and [Ant. Day Of Delivery]

Pre_load:

Load*,

if (([Actaual Delivery Date]- [Ant. Day Of Delivery])=0,'Delivered on Time','Late Delivery') as [Delivery Period];

Load

AutoNumber([Day Of Order]) as [MonthID];

Main:

LOAD

[Customer ID],

Customer,

[Product Line],

[Product Group],

Product,

Date(date#([Day Ordered],'DD/MM/YYYY')) as [Day Of Order],

[No. Of Sales],

[Sales Rep.],

Date(Date#([Promised Day of Delivery],'DD/MM/YYYY')) as [Ant. Day Of Delivery],

Date(Date#([Actual Day Delivered],'DD/MM/YYYY')) as [Actaual Delivery Date],

[Division In Country],

Country,

Region,

Province,

Price,

AutoNumberHash256([Customer ID], Customer) as [Repeat Customers]

FROM

[ACME Inc. 26 Jul 2016.xlsx]

(ooxml, embedded labels, table is [BMW (65)]);

//Create Master Calendar to get Month, Year, Week, Day and Quarter

Mas_Cal:

LOAD

Date([Day Of Order]) as [Day Of Order],

Year([Day Of Order]) as Year,

'Q' & Ceil(Month([Day Of Order])/3) as Quarter,

Month([Day Of Order]) as Month,

Day([Day Of Order]) as Day,

Week([Day Of Order]) as Week;

LOAD

Date(MinDate + Iterno() -1) as [Day Of Order]

While(MinDate + Iterno() -1)<=Num(MaxDate);

LOAD

Min([Day Of Order]) as MinDate,

Max([Day Of Order]) as MaxDate

Resident Main;

Thanks in advance!

Re: Last Year To Date

sunny_talwar — Sun, 31 Jul 2016 20:19:32 GMT

Well AutoNumber will assign sequential number in the order of the load statement. For example if you have dates like this

Date, AutoNumber

13 Dec 2015, 1

12 Dec 2015, 2

14 Dec 2015, 3

21 Dec 2015, 4

AutoNumber doesn't know what sort order you want. In order to get a desired AutoNumber, you will need to sort the load statement in a resident load using Order By Statement.

Try something along these lines:

Main:

LOAD [Customer ID],

Customer,

[Product Line],

[Product Group],

Product,

Date(Date#([Day Ordered],'DD/MM/YYYY')) as [Day Of Order],

[No. Of Sales],

[Sales Rep.],

Date(Date#([Promised Day of Delivery],'DD/MM/YYYY')) as [Ant. Day Of Delivery],

Date(Date#([Actual Day Delivered],'DD/MM/YYYY')) as [Actaual Delivery Date],

[Division In Country],

Country,

Region,

Province,

Price,

AutoNumberHash256([Customer ID], Customer) as [Repeat Customers]

FROM [ACME Inc. 26 Jul 2016.xlsx]

(ooxml, embedded labels, table is [BMW (65)]);

Pre_load:

LOAD *,

If(([Actaual Delivery Date] - [Ant. Day Of Delivery]) = 0, 'Delivered on Time', 'Late Delivery') as [Delivery Period];

LOAD *,

AutoNumber([Day Of Order]) as [MonthID]

Resident Main

Order By [Day Of Order];

DROP Table Main;

And then you can continue onto creating a calendar for yourself the same way you have done this before.

HTH

Best,

Sunny

Re: Last Year To Date

kaygee28 — Mon, 01 Aug 2016 07:09:57 GMT

Oh i see Sunny you're a genious it actually worked!

I guess I need to start broadening my thinking a bit.