creating a time series

[kaygee28]

Good day

I'm not sure what I'm doing wrong currently or maybe my qlik app isn't reading my column as a date field for some reason, cause I want to create a time series for my app and my output is the total number of records for thast selection, for instance if I make the dimension the year or if I make it the month see image below here is the output I get.

The expression is above the image.

I created a master calendar in order to create the day, week, month and year but the app isn't reading all of this.

Here is code below:

gee:

LOAD [Year of Entrance],

     idnumber,

     surname,

     firstnames,

     GenderDesc,

     email,

     cellno,

     Date(Date#([Date Unsubscribed],'YYYY/MM/DD')) as [Date Unsubscribed]

FROM

[CC Entry File 2013 .xlsx]

(ooxml, embedded labels, table is Sheet1);

jee:

concatenate(gee)

LOAD [Year of Entrance],

     idnumber,

     surname,

     firstnames,

     GenderDesc,

     email,

     cellno,

     [Date Unsubscribed]

FROM

[CC Entry File 2014.xlsx]

(ooxml, embedded labels, table is PrintFile_12_201603011306);

vee:

concatenate(gee)

LOAD [Year of Entrance],

     idnumber,

     surname,

     firstnames,

     GenderDesc,

     email,

     cellno,

     [Date Unsubscribed]

FROM

[CC Entry File 2015.xlsx]

(ooxml, embedded labels, table is PrintFile_20_201603011238);

LOAD

Date([Date Unsubscribed]) as [Date Unsubscribed],

Year([Date Unsubscribed]) as Year,

'Q' & Ceil(Month([Date Unsubscribed])/3) as Quarter,

Month([Date Unsubscribed]) as Month,

Day([Date Unsubscribed]) as Day,

Week([Date Unsubscribed]) as Week;

LOAD

Date(MinDate + Iterno() -1) as [Date Unsubscribed]

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

LOAD

Min([Date Unsubscribed]) as MinDate,

Max([Date Unsubscribed]) as MaxDate

Resident gee;

Fee:

NoConcatenate

load*

resident gee;

drop table gee;

[Colin-Albert]

Have you tried repeating the date(date#)) expression for all occurences of the date field?

     Date(Date#([Date Unsubscribed],'YYYY/MM/DD')) as [Date Unsubscribed]

Have a look at this post  Why don’t my dates work?

[stabben23]

Hi,

remove this part from Your script, this is overkill if you do a resident load from gee. Min and Max will here transfer Your

MinDate MaxDate to a numeric value and that maybe messup the calendar for you.

LOAD

Date(MinDate + Iterno() -1) as [Date Unsubscribed]

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

LOAD

Min([Date Unsubscribed]) as MinDate,

Max([Date Unsubscribed]) as MaxDate

[kaygee28]

Okay guys thanks a lot for your help, Colin your option didn't work cause it makes all my list boxes disappear for some reason, while Staffan solved 60% of my problem so I can get graphs for all the Entrances except for the 2013 entrance in this field [Year of Entrance], so I'm guessing cause I loaded 2013 first then this code has an issue,

gee:

LOAD [Year of Entrance],

     idnumber,

     surname,

     firstnames,

     GenderDesc,

     email,

     cellno,

     Date(Date#([Date Unsubscribed],'YYYY/MM/DD')) as [Date Unsubscribed]

FROM

[CC Entry File 2013 .xlsx]

(ooxml, embedded labels, table is Sheet1);

[kaygee28]

please see app attached to see what I'm talking about. !

[Colin-Albert]

Hi Hagiso,

You could try adding an "exit script;" statement after loading the 2013 data into gee:  to view the data you have loaded at this stage to see if that Date Unsubscribed field holds valid dates.

Is the format of the Date Unsubscribed data different in the CC Entry File 2013 spreadsheet compared to the other year's data?

Is this the reason you are using a date(date# )) function for 2013 but not for the other years?

If you are not loading valid dates for 2013, then the calendar processing in the while loop will not process the 2013 data correctly.

[kaygee28]

Hi Colin Thanks alot for your help, I verified the date field in the 2013 file and it seems to be correct and consistent with the other files so not really sure what the problem might be, with regards to utilizing the date(date#() function, if i declare the statement throughout the load statements then my list boxes disappear and hence I cannot make the necessary selections for my charts. maybe I'll need to do 2013 on its own.

[Colin-Albert]

If the date field for 2013 is consistent with the other files, then surely the date function should be the same for all files you are loading.