Time fields

[#1]

Hi,

I'm a qlikview enduser and I have a question regarding a time dimension and more precisely a delay time

I must calculate a time field but what's the best solution?

I create a script or a calculated dimension in a pivot table? And how

I have 2 fields actualdate and actduedate

For instance, if actduedate>actualdate then delay but how must I create this?

Thanks

Best regards

[its_anandrjs]

Hi,

You need to create calculated espression for both dates you need to use some thing like this in your in code

1. If you create this in load script you need to load a resident table of your table like

Load

*,

If ( actduedate > actualdate , 'Delay' , 'Ontime') as Status

Resident YourTable;

2. If you want to create calculated dimension so like this

If ( actduedate > actualdate ), 'Delay' , 'Ontime') you need to put some code like this and you get the

But best is in Load script

Regards

Anand

[#3]

re,

But my 2 fields are in 2 different tables (actduedate in Act and actualdate in Lig)

How I can do?

Thanks

[its_anandrjs]

Hi,

Ok you need to create a pivot table on that when you put the fields actualdate and actduedate in next to both the field type a calculated code for status like

if(actduedate>actduedate1,'Delay','Ontime') and named it what ever you like

or also you check if there is relation ship between two tables or not.

Regards,

Anand

[#5]

re,

Excuse me I understand but with delay I'm just an enduser

I try with your code

Load

*,

If ( actduedate > actualdate ), 'Delay' , 'Ontime') as Status

Resident YourTable;

but I can't run

Can you help me?

[its_anandrjs]

Hi,

Suppose there is two different table but there is common file AccNo so reload it and see the code

TableA:

LOAD Date(actduedate) as actduedate,

     AccNo

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sheet23);

TableB:

LOAD Date(actualdate) as actualdate,

     Value,

     AccNo

FROM

..\Data.xlsx

(ooxml, embedded labels, table is Sheet25);

And after of these when you create a pivot type this code

if(actduedate>actualdate,'Delay','Ontime') for status

See the attached sample

Regards,

Anand

[#7]

Hi,

I try your solution but I can't run the code.

I have forgotten to give an important detail sometimes I can't calculate because of a lack date.

Example

Script

//ACTSQL SELECT ACTACTION,
    ACTDUEDATE,
    Day(ACTDUEDATE) as Duedate,
    ACTLIBELLE,
    ACTNOTIFICATIONDATE,
    EVIDENCE,
    RESPONSIBLE
FROM ACT;

//LIGNESQL SELECT ACTUALDATE,
       Day (ACTUALDATE) as Aldate,
    LIGNE,
    NA,
    NO,
    NUMERO,
    QUESTION as question,
    STATUS,
    YES
FROM LIGNE;

  =if(Duedate>Aldate,'Delay','Ontime') 

Impossible to visualize a result:

Problemwith datas:

[its_anandrjs]

Hi,

Is this your solution or not that i suggest you and let me know what is the status for the application.

Regards,

Anand

[#9]

Hi,

Yes, it looks like to my solution but How do I handle empty cells? and tell impossible to calculate or not to display result?

Thanks a lot

[its_anandrjs]

Hi,

Thanks, Yes it is possible to handle empty cells that are called null cells for handle that just select that field in dimension tab and select Suppress When Value Is Null option check marked if it is not work there is any other field so select same option for both.To remove null values from any field it is best way.

Hope this helps you.

Regards

Anand