# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for
Did you mean:
Creator II

## Calculating the number of Days between two dates

Hi everyone - I thought I was doing something relatively easy.......

I'm trying to calculate the number of days between two dates with the idea that I will create a dimension to accumulate the number of cases in aging buckets.  (0-30, 31-60, 61-90 etc....)   I thought I understood this well enough but I guess I don't.  I'm a novice user.

I've created a field as part of my script to retain the current date ( Today() as "CurrentDate";), but when I try to create the field 'Days' by using this formula (CurrentDate - DATE_INVESTIGATION_STARTED) I'm getting various errors.

When I try to use it in the script after creating "CurrentDate" I'm getting errors for 'field not found'.

I'm just not sure what I'm doing wrong, or if I'm creating this in the proper manner.

Any input would help!!  Time Appreciated!!!

21 Replies
Creator II
Author

Got it!!  Thanks for the learning opportunity!

Creator II
Author

Now that you solved the days calculation for me, how would you suggest I calculate by 30, 31-60, 61-90, 91-120, 120+ buckets.   Within the script or as a calculated dimension??

MVP

I suggest doing it in the script in a preceding load:

If(Days <= 30, Dual('30', 1),

If(Days <= 60, Dual('31-60', 2),

If(Days <= 90, Dual('61-90', 3),

If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

Today() - "DATE_INVESTIGATION_STARTED as Days

FROM Source

Creator II
Author
 Sunny T wrote: I suggest doing it in the script in a preceding load: LOAD *,           If(Days <= 30, Dual('30', 1),           If(Days <= 60, Dual('31-60', 2),           If(Days <= 90, Dual('61-90', 3),           If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets; LOAD yourField,           Today() - "DATE_INVESTIGATION_STARTED as Days FROM Source

In the second load statement above you reference 'yourField'  Since you have named the calculation for Buckets and Days is defined, what field name am I putting there???

MVP

YourField is a placeholder for all the fields that are coming out of your SQL Load that you want to load into the application. If you want to load everything, then may be you want to use a *

If(Days <= 30, Dual('30', 1),

If(Days <= 60, Dual('31-60', 2),

If(Days <= 90, Dual('61-90', 3),

If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

Today() - "DATE_INVESTIGATION_STARTED as Days;

SQL SELECT *

FROM Source

Creator II
Author

I understand what you have presented but I'm clearly setting up something improperly.........here is my script as of now.  I'm getting a 'Field Not Found' error for the Days field I'm creating.  I'm confused as to why since I'm not using any prevuously calculated field???  What am I doing wrong?

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=qlickview_user;Data Source=Sched35DEV;Extended Properties="DSN=Sched35DEV;Description=Sched35DEV;UID=qlickview_user;APP=QlikView;WSID=US1900050;DATABASE=ARCPLAN_MET_Sch35";Initial Catalog=ARCPLAN_MET_Sch35] (XPassword is IPCVBJNEYJOcUZQOWRRMGEB);

Table:

If(Days <= 30, Dual('30', 1),

If(Days <= 60, Dual('31-60', 2),

If(Days <= 90, Dual('61-90', 3),

If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets,

CASE_NUMBER&YR&PERIOD AS CasePeriod,

Month(DATE_CLOSED) as Mon,

"SI_SCHED_STATUS_ID",

"ARC_OBJECT_NAME",

"ORDER_NUM",

PERIOD,

YR,

AreaCurr,

AreaCode,

LOCATION,

"CASE_NUMBER",

"DATE_REPORTED",

"DATE_INVESTIGATION_STARTED",

"AMOUNT_AT_RISK_USD",

SUBJECT,

"CASE_CONTACT",

PRODUCT,

"CASE_TYPE_AREA",

"COVERAGE_TYPE",

"INT_EXT",

"CASE_DETAILS",

ACTION,

"AMOUNT_RECOVERED_USD",

"AMOUNT_PREVENTED_USD",

"CASE_STATUS",

"DATE_CLOSED",

"NA_FLAG",

"FINAL_FLAG",

"SECURE_FLAG",

Today() as "CurrentDate",

Today() - "DATE_INVESTIGATION_STARTED" as "Days";

SQL SELECT *

FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";

MVP

Try this:

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=True;User ID=qlickview_user;Data Source=Sched35DEV;Extended Properties="DSN=Sched35DEV;Description=Sched35DEV;UID=qlickview_user;APP=QlikView;WSID=US1900050;DATABASE=ARCPLAN_MET_Sch35";Initial Catalog=ARCPLAN_MET_Sch35] (XPassword is IPCVBJNEYJOcUZQOWRRMGEB);

Table:

If(Days <= 30, Dual('30', 1),

If(Days <= 60, Dual('31-60', 2),

If(Days <= 90, Dual('61-90', 3),

If(Days <= 120, Dual('91-120', 4), Dual('120+', 5))))) as Buckets;

Month(DATE_CLOSED) as Mon,

"SI_SCHED_STATUS_ID",

"ARC_OBJECT_NAME",

"ORDER_NUM",

PERIOD,

YR,

AreaCurr,

AreaCode,

LOCATION,

"CASE_NUMBER",

"DATE_REPORTED",

"DATE_INVESTIGATION_STARTED",

"AMOUNT_AT_RISK_USD",

SUBJECT,

"CASE_CONTACT",

PRODUCT,

"CASE_TYPE_AREA",

"COVERAGE_TYPE",

"INT_EXT",

"CASE_DETAILS",

ACTION,

"AMOUNT_RECOVERED_USD",

"AMOUNT_PREVENTED_USD",

"CASE_STATUS",

"DATE_CLOSED",

"NA_FLAG",

"FINAL_FLAG",

"SECURE_FLAG",

Today() as "CurrentDate",

Today() - "DATE_INVESTIGATION_STARTED" as "Days";

SQL SELECT *

FROM "ARCPLAN_MET_Sch35".dbo."User_Input_Sched_35";

Creator II
Author

MVP

No problem at all

Creator II
Author

Hello Sunny T - since we completed this thread I've had to make some changes to the way I'm loading the data due to some funky characters.  I'm now using ACL to prep my data and have it exporting to XML which is what I'm using as my Qlik import.  My script is below.  There are no error messages when creating the Buckets, but when I go to add it as a dimension its not available as a selection.  What am I doing wrong?

Directory;

// Start of [Sched35QlikStagingTable.xml] LOAD statements

element:

type,

%Key_RECORDS_BFE9EA7045DD507E    // Key to parent table: RECORDS

FROM Sched35QlikStagingTable.xml (XmlSimple, Table is [RECORDS/schema/element/complexType/sequence/element/complexType/sequence/element]);

RECORD:

CASE_NUMBER,

PERIOD,

YR,

AreaCurr,

LOCATION,

DATE_REPORTED,

DATE_INVESTIGATION_STARTED,

AMOUNT_AT_RISK_USD,

SUBJECT,

CASE_CONTACT,

PRODUCT,

CASE_TYPE_AREA,

COVERAGE_TYPE,

INT_EXT,

ACTION,

AMOUNT_RECOVERED_USD,

AMOUNT_PREVENTED_USD,

CASE_STATUS,

DATE_CLOSED,

ARC_OBJECT_NAME,

NA_FLAG,

ConsFlag,

Company,

Region,

Sub_Region,

CaseClosedDate,

Days,

Loss,

CaseClosedDate - DATE_INVESTIGATION_STARTED as CalcDays,

%Key_RECORDS_BFE9EA7045DD507E    // Key to parent table: RECORDS

FROM Sched35QlikStagingTable.xml (XmlSimple, Table is [RECORDS/RECORD]);

IF(CalcDays <= 30, Dual('Under 30', 1),

IF(CalcDays <= 60, Dual('31-60', 2),

IF(CalcDays <= 90, Dual('61-90', 3),

IF(CalcDays <= 120, Dual('91-120', 4),

IF(CalcDays > 120, Dual('120+', 5)))))) as Buckets;

RECORDS:

xsi:noNamespaceSchemaLocation,

[schema/id] as id,

[schema/xmlns:xs] as xmlns:xs,

[schema/element/name],

[schema/element/complexType/sequence/element/name] as [sequence/element/name],

[schema/element/complexType/sequence/element/minOccurs] as minOccurs,

[schema/element/complexType/sequence/element/maxOccurs] as maxOccurs,

[schema/element/complexType/sequence/any/namespace] as namespace,

[schema/element/complexType/sequence/any/processContents] as processContents,

%Key_RECORDS_BFE9EA7045DD507E    // Key for this table: RECORDS

FROM Sched35QlikStagingTable.xml (XmlSimple, Table is [RECORDS]);

// End of [Sched35QlikStagingTable.xml] LOAD statements