Difference between two date

[#1]

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

[swuehl]

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.

[sukydhak]

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

[#4]

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

[#5]

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?

[sukydhak]

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

Suky

[#7]

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?

[swuehl]

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

[ramoncova06]

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 ?