使用over函数的Spotfire日期差 [英] Spotfire date difference using over function

问题描述

我有以下数据集:

Item  ||  Date       || Client ID || Date difference
A     ||  12/12/2014 || 102       || 
A     ||  13/12/2014 || 102       || 1
B     ||  12/12/2014 || 141       || 
B     ||  17/12/2014 || 141       || 5

当客户ID相同时，我想计算两个日期之间的年差.我可以在计算列中使用什么表达式来获取该值?

I would like to calculate the difference in years between the two dates when the client ID is the same. What expression can I use in a calculated column to get that value?

更新

嗨

这将是计算出的预期值.我的表有大约300,000条记录，但没有特定顺序.使用此公式之前，我必须对物理表进行排序吗?我从另一个发现的例子中使用了这个例子，我的实际文件没有item列.它仅是客户ID和交易日期.再次感谢您的帮助！

This would be the intended values calculated. My table has approximately 300,000 records in no particular order. Would I have to sort the physical table before using this formula? I used this example from another I found, my actual file has no item column. It is only the client ID, and date of the transaction. Thanks again for the help!

ClientId    Date        Days
 102         2014.12.12  0
 102         2014.12.13  1
 141         2014.12.12  0
 141         2014.12.17  5
 123         2014.12.01  0
 123         2014.12.02  1
 123         2014.12.04  2

推荐答案

编辑2015.07.15

EDIT 2015.07.15

知道了，所以如果您希望与上一个客户日期对有所不同.该表达式将为您提供上面列出的表格.可读性的间距:

got it, so if you want the difference from the last customer-date pair. this expression will give you the table you've listed above. spacing for readability:

DateDiff('day',
  First([Date) OVER (Intersect([ClientId], Previous([Date]))),
  [Date]
)

编辑2015.07.13

EDIT 2015.07.13

如果要减小此值以便可以准确地汇总[Days]，则可以将上述表达式用If()括起来.我将添加一些间距以使其更具可读性:

if you want to reduce this so that you can accurately aggregate [Days], you can surround the above expression with an If(). I'll add some spacing to make this more readable:

If(
    [Date] = Min([Date]) OVER Intersect([ClientId], [Item]), 
    DateDiff( 'day', 
        Min([Date]) OVER Intersect([ClientId], [Item]), 
        Max([Date]) OVER Intersect([ClientId], [Item])
    )
    , 0
)

英文:如果此行中[Date]列的值与[ItemId]和[ClientId]组合的最早日期相匹配，则将第一个和最后一个[Date]之间的天数相差天数此[ItemId]和[ClientId]组合；否则，请输入零."

in English: "If the value of the [Date] column in this row matches the earliest date for this [ItemId] and [ClientId] combination, then put the number of days difference between the first and last [Date] for this [ItemId] and [ClientId] combination; otherwise, put zero."

其结果类似于:

Item    ClientId    Date        Days
A       102         2014.12.12  1
A       102         2014.12.13  0
B       141         2014.12.12  5
B       141         2014.12.17  0
C       123         2014.12.01  2
C       123         2014.12.02  0
C       123         2014.12.03  0

警告，过滤器可能会破坏此计算.例如，如果您基于[日期]进行过滤，并以上表为例，则过滤掉OUT.2014.12.13之前的所有日期，则Sum([Date])将是7而不是8(因为第一行是过滤掉.)

WARNING that filters may break this calculation. for example, if you are filtering based on [Date] and, with the above table as an example, filter OUT all dates before 2014.12.13, Sum([Date]) will be 7 instead of 8 (because the first row has been filtered out).

您可以使用Spotfire的OVER函数查看跨行具有通用ID的数据点.

you can use Spotfire's OVER functions to look at data points with common IDs across rows.

您似乎每个客户ID和项目ID只有两行，这对我们有帮助！使用以下公式:

it looks like you've only got two rows per Client ID and Item ID, which helps us out! use the following formula:

DateDiff('day', Min([Date]) OVER Intersect([ClientId], [Item]), Max([Date]) OVER Intersect([ClientId], [Item]))

这将为您提供一列，其中每行中两个日期之间的天数相差天数:

this will give you a column with the number of days difference between the two dates in each row:

Item    ClientId    Date        Days
A       102         2014.12.12  1
A       102         2014.12.13  1
B       141         2014.12.12  5
B       141         2014.12.17  5

这篇关于使用over函数的Spotfire日期差的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！