topic Re: Difference between two date in QlikView

Difference between two date

Fri, 06 Jun 2014 18:25:41 GMT

Hi all,

i need to calculate difference between two date( CreatedDate_Attività and LastDateModified) that they have different filter conditions.

I try to do this with only function but it doesn't work.

in particular i want to calculate difference between :

Expression A :

=only({<IdAttività=P({<Categoria__c={'Attivazione'},NameAttività={'Credit Check','Credit check'},State__c={'Nuovo'},Stato_Pipeline={'1 Check Doc','2 Credit Check','3 Check Call','3 Check Letter','4 Gest. UTF','5 Invio Recessi','6 DL','6 DL KO','7 Non fatturabile','8 Fatturabile','0 Annullamenti'}>})+

p({<Categoria__c={'Attivazione'},Sottocategoria__c={'Contatore Posato Gas','Contatore Posato Power','Voltura','Switch attivo','Posa nuovo contatore Gas','Nuovo allaccio e attivazione Power'},NameAttività={'Credit Check','Credit check'},State__c={'Attivo'},Stato_Pipeline={'7 Non fatturabile'}>})>} CreatedDate_Attività)

Expression B:

=only({<IdAttività =P({<Categoria__c={'Attivazione'},NameAttività={'Invio a IDM e registrazione Esito'},LastModifiedDate=,State__c={'Nuovo'},StatoAttività={'Eseguita'},Esito__c={'OK'},Stato_Pipeline={'1 Check Doc','2 Credit Check','3 Check Call','3 Check Letter','4 Gest. UTF','5 Invio Recessi','6 DL','6 DL KO','7 Non fatturabile','8 Fatturabile','0 Annullamenti'}>})

+p({<Categoria__c={'Attivazione'}, NameAttività={'Invio a IDM e registrazione Esito'},LastModifiedDate=,State__c={'Attivo'},Esito__c={'OK'}, StatoAttività={'Eseguita'},Stato_Pipeline={'7 Non fatturabile'}>})>} LastModifiedDate)

How can i do?

Thanks a lot

Re: Difference between two date

swuehl — Sat, 07 Jun 2014 10:14:10 GMT

Do both expressions return something?

Where / in which context are you using these expressions (in a chart (with which dimensions?), a text object)?

Have you ensured, that both dates are have indeed a numeric representation?

http://community.qlik.com/blogs/qlikviewdesignblog/2013/02/19/why-don-t-my-dates-work

If both expressions do return a date, i.e. something that have a numeric representation so you can do calculations on, you should be able to just subtract the one from the other:

= only(... CreatedDate_Attivita) - only(... LastModifiedDate)

This should give you the difference as fraction of days.

Re: Difference between two date

sukydhak — Sat, 07 Jun 2014 11:12:25 GMT

First question is does your statement return a value?

You can done the difference by using the label or column of [Expression A] - [Expression B] .

The other option would be to do the calculation in the script and that way you will have a field with the value for the UI.

CreatedDate_Attivita-LastModifiedDate as Date_Diff,

on the UI you can do this

=only(Date_Diff)

which will also be faster and impact lest on the server resource

Suky

Re: Difference between two date

Sat, 07 Jun 2014 11:48:01 GMT

This expressions returns both number of day between LastModifiedDate and CreatedData_Attività.

I use this expression in a linear table.

I want to know number of day between those two dates, but LasModifiedDate has a specific filter ... and Created Data has other specific filter, so i can't use in one only function.

I try to do only... - only... but it doesn't work.

Can you help me?

Thanks

Re: Difference between two date

Sat, 07 Jun 2014 11:52:57 GMT

The single expression return a date with specific condition ( Categoria = {'Attivazione'} etc) that it is different for CreatedData and LastDateModified.

I can't do CreatedDate_Attività - LastModifiedDate, because i want:

CreatedDate_Attività for Categoria = attivazione and NameAttività={'Credit Check','Credit check'} etc.

LastModifiedDate for CaTegoria = Attivazione and NameAttività={'Invio a IDM e registrazione Esito'} etc.

How can i do?

Re: Difference between two date

sukydhak — Sat, 07 Jun 2014 12:14:23 GMT

If you upload a sample of the file I can have a look

Suky

Re: Difference between two date

Sat, 07 Jun 2014 14:26:54 GMT

This is an example. I want difference between CreatedDate and LastModified Date.

How you can see in the image, The NameAttività is different for CreatedDate and LastModifiedDate.

How can i make difference between those two dates?

Re: Difference between two date

swuehl — Sat, 07 Jun 2014 14:56:05 GMT

I think you would need to evaluate your difference-of-dates expression in a calculation scope where each single expression can be evaluated. For example, if each of your expressions limits the values of NameAttivita to different, disjunct values, and you are using NameAttivita as dimension, the calculation scope of both expressions is disjunct also.

I thin you would either need to remove dimensions, like NameAttivita, or try using the TOTAL qualifier with a field list, like

= only({<... >} TOTAL<CaseNumber,Sottocategoria, Commodity__c,Target__C> CreatedDate_Attivita) - only({<... >} TOTAL<CaseNumber,Sottocategoria, Commodity__c,Target__C> LastModifiedDate)

But it's quite hard to help you without knowing your data model and requirements, so if possible upload a sample application together with your expected result.

Preparing examples for Upload - Reduction and Data Scrambling

Re: Difference between two date

ramoncova06 — Sat, 07 Jun 2014 15:12:29 GMT

I normally would do this type of things in the script or data model, at the end this will even help with the performance of the document.

I am assuming your data looks like this

Is there a reason as to why you cannot do a left join to append the a field with lastmodified date to your registration date ?