Problem while Calculating date difference in a table using above().

[nirav_bhimani]

Hi Everyone,

I am using the following expression, Using that I am not getting the output as I want, In that when container no is changed then also its calculating event difference with the one above eventdate of different container. I want first event difference should be –  or 0 for each container rather than calculating event diff with its above event date of each, I want output as following table.

SET ANALYSIS EXPRESSION:

if(RowNo(TOTAL )=1,null(), Interval(Timestamp (Above (Total
EVENT_DATE) - ( EVENT_DATE) ),'D hh:mm') )

Straight Table:

Container No.	Event	Event Date	Date Difference(In days)
a	aa	01-01-2013 13:00	-
a	bb	02-01-2013 14:00	1
a	cc	05-01-2013 16:00	3
a	dd	06-01-2013 18:00	1
b	aa	05-01-2013 01:00	-
b	ee	08-01-2013 00:00	3
b	dd	10-01-2013 01:00	2
b	cc	15-01-2013 13:00	6
b	ee	17-01-2013 13:00	2
c	aa	10-01-2013 15:00	-
c	bb	11-01-2013 02:00	0
c	cc	16-01-2013 03:00	5
c	dd	18-01-2013 05:00	2

Please provide any solution for the same.

Thanks & Regards, 

Nirav Bhimani

[Gysbert_Wassenaar]

You should do this in the script, not in an expression. See attached qvw.

But if you feel you must use an expression try this one: if(above(total [Container No.])=[Container No.], interval( [Event Date]-above(total [Event Date]),'d')). And make sure your chart is sorted on Container No. first and then Event Date.

[swuehl · Accepted solution]

If your interval calculation needs to be sensitive to selection (i.e. you need to do this in the chart), I think all you need to do is using above() without the TOTAL qualifier and using only two dimensions, [Container No.] and [Event Date] to create a line per single distinct event.

Then, you can show your Event name by using an expression like

=only(Event)

and your intervals like

=round([Event Date]-above([Event Date]))

(I wasn't quite sure how you want to handle the fractions of a day, the expression is replicating the result table you posted above. Another idea is to use

=floor([Event Date])-above(floor([Event Date]))

Using two dimensions, the standard behaviour of above() function will return what you want on change of Container No. (assuming that your columns are sorted by [Container No.], [Event Date] asc).

See also attached.

Stefan