A time field calculate

[#1]

Hi,

I'm a qlikview enduser and I must create code

I have a question regarding a time dimension and moreprecisely a date to maturity date.

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

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

I have a field actnotif and I must create an Alert or Toolate for manager

  if ActNotif - ActdueDate<= 1 month then display delay in a pivot table but how must I create this?

if ActNotif - ActdueDate=> 1 month then display toolate

However, I want to consider the current day

For instance

actnotif         actduedate     result

01/09/2011    15/09/2011     Alert

01/09/2011     01/10/2011    Alert

01/06/2011     01/07/2011    Toolate

Thanks

Best regards

[swuehl]

If your dates are real date fields in QV, they have a number representation and you can just subtract dates.

So you could probably use as expression in your chart / table

=if(ActNotif - ActdueDate <= 30, 'Delay','too late')

with a fixed 30 days for a month, or use

=if(addmonths(ActNotif,1) <= ActdueDate, 'Delay','too late')

to add a month depending on your input date.

I hav not understood what you mean with considering the current day, maybe using today() function?

Regards,

Stefan

[#3]

I forgot to show my script.

I use "Month"

Month (ACTDUEDATE) as ActdueDate,

   Month (ACTNOTIFICATIONDATE) as ActNotif,

But it can't distinguish 'delay' and 'too late' in my chart!

My calculation considers just the current month (september)

For instance

actnotif         actduedate     result

01/09/2011    15/09/2011     Alert

01/09/2011     01/10/2011    Alert

01/06/2011     01/07/2011    Toolate

Thanks

Best regards

[swuehl]

Sorry, I still don't understand:

actnotif         actduedate     result

01/09/2011    15/09/2011     Alert

...

Here, the dates are not derived from using Month(...) in the script, are they?

I also don't understand how your calculation considers the current month, do you want to limit the displayed dates of actnotif and/ or actduedate to the current month? In your example, neither actnotif nor actduedate are:

e.g. 01/06/2011     01/07/2011    Toolate

IMHO, Month() function is good for displaying the month of a name, but not necessarily for calculations / conditions. I would recommend keeping the Dates ACTDUEDATE and ACTNOTIFICATIONDATE in your data model, maybe changing the script to

Month (ACTDUEDATE) as ActdueMonth,

Month (ACTNOTIFICATIONDATE) as ActNotifMonth,

Date(ACTDUEDATE) as ActdueDate,

Date(ACTNOTIFICATIONDATE) as ActNotifDate,

then you should be able to reuse my expressions from above.

Regards,

Stefan

P.S: if you are only interested in the Month ( not the day of month) for calculation your alert, I would use monthstart(ACT....DATE) as ....

in your script, so you get the monthstart as Date and you could easily calculate your alerts, even across year bounderies (which Month() will have problems with)).

[#5]

Hi,

Yes, I want to do it, get the monthstart as Date and calculate my alerts.

but I can't run my script with: MonthStart ('ACTNOTIFICATIONDATE', -1) as ActNotifStart,

How could I use the function Monthstart?

I must use one date?

Thanks

Best regards

[SunilChauhan]

use this

MonthStart (ACTNOTIFICATIONDATE, -1)

or for before on month u ca use thsi

MonthStart (addmonths(ACTNOTIFICATIONDATE, -1))

[swuehl]

try this (eliminating the Quotes and the second parameter (if you don't want the monthstart of the previous month):

monthstart(ACTNOTIFICATIONDATE) as ActNotifMonthStart