Tableau中的数组计算，maxif例​​程 [英] Array calculation in Tableau, maxif routine

问题描述

我对 Tableau 还很陌生，我正在努力构建一些可以在 Excel 中轻松实现的例程(尽管对于大数据集需要很长时间).

I'm fairly new to Tableau, and I'm struggling in building some routines that could be easily implemented in Excel (though it would take forever for big sets of data).

所以这里是交易，考虑具有以下字段的数据集:

So here is the deal, consider a dataset with the following fields:

int [id_order] -> 销售订单的id(最深层次，只有id_order的唯一条目)int [id_client] -> 因为我想知道是谁买的日期 [purchase_date] -> 客户购买产品的时间

int [id_order] -> id of the sales order (deepest level, there are only unique entries of id_order) int [id_client] -> as I want to know who bought it date [purchase_date] -> when the customer bought the product

我想知道的是，对于每个订单，客户最后一次(如果有的话)购买东西是什么时候.换句话说，小于当前购买日期的用户的最高购买日期是什么.

What I want to know is, for each order, when was the last time (if ever) the client has bought something. In order words, what is the highest purchase_date for that user that is smaller than current purchase_date.

在excel中，方法很简单(但同样，效率不高){=max(if(id_client=B1,if(purchase_order)

有没有办法在 Tableau 中进行这种计算?

In excel, approach is simple (but again, not efficient) {=max(if(id_client=B1,if(purchase_order

Is there a way to do this kind of calculation in Tableau?

推荐答案

只是为了注册解决方案，以防有人有同样的疑问.

Just to register the solution, in case someone has the same doubt.

所以，基本上我找到的解决方案使用表计算，直到在工作表上调用它才会计算(并且仅在工作表的上下文中计算).这有点限制，所以我要做的是创建一个包含我需要的所有字段(+ 表计算所需的内容)的工作表，然后导出数据(到 mdb)并连接到这个新文件.

So, basically the solution I found use table calculation, which is not calculated until it's called on a sheet (and is only calculated on the context of the sheet). That's a little bit limiting, so what I do is create a sheet with all the fields I need (+ what is necessary for the table calculation) then export the data (to mdb) and connect to this new file.

因此，对于我的示例，正​​确的表计算是(让我们将其命名为 last_order_date):

So, for my example, the right table calculation is (let's name it last_order_date):

LOOKUP(MAX([purchase_date]),-1)

说明.MAX() 是必需的，因为查找(和所有表计算)不能直接处理数据，只能处理聚合.你可以使用 sum、avg、max、attr，任何适合你的.就我而言，只有 1 个对应关系，任何函数都可以正常工作并返回相同的值.

Explanations. The MAX() is necessary because Lookup (and all table calculations) does not work with data directly, only with aggregations. You can use sum, avg, max, attr, whatever suits you. As in my case there will be only 1 correspondence, any function will do just fine and return the same value.

-1 表示我正在查找当前条目(表的，如您定义的那样)之前的元素.如果是 FIRST()，它将查找表的第一个条目，而 LAST() 将查找最后一个.

The -1 indicates that I'm looking for the element immediately before the current entry (of the table, as you define it). If it were FIRST(), it would go for the first entry of the table, and LAST() would go for the last.

现在，我必须把它放在一张纸上.所以我将带来字段 id_client、id_order、purchase_date 和 last_order_date.

Now, I have to put it on a sheet. So I'll bring the fields id_client, id_order, purchase_date and last_order_date.

然后我必须定义我的表计算last_order_date(编辑表计算)的参数.我将转到计算使用并选择高级.现在我将进行分区:id_client，并解决所有其他问题.那会做什么?这意味着 Tableau 将为每个 id_client 创建临时表，表计算将使用这些表作为参数.

Then I have to define the parameters of my table calculation last_order_date (Edit Table Calculation). I'll go to Compute using and choose advanced. Now I'll do Partitioning: id_client, and addressing all the rest. What will that do? This mean Tableau will create temporary tables for each id_client, and table calculations will use those tables as parameter.

此外，我将按字段购买日期、最大(再次聚合问题)和升序进行排序，以确保我的条目按时间顺序排列.

Additionally, I will Sort by field purchase_date, Max (again the aggregation issue) and ascending, to guarantee my entries are in chronological order.

现在，它会做什么?对于每个条目，它将访问 id_client 的表，并检查当前条目(正在评估)之前的purchase_date 是什么，这正是我需要的.

Now, what will it do? For each entry it will access the table of the id_client, and check what was the purchase_date that is immediately before the current entry (that is being assessed), exactly what I need.

为了避免在可视化中花费 Tableau 处理时间，我经常将所有字段放在详细信息中(并且在屏幕上不留任何内容)，使用条形图(这很好，因为它可以让我看到数据).然后我将它导出到 mdb，然后再次连接到它.不幸的是，Tableau 不会直接导出到 tde.

To avoid spending Tableau processing in Visualization, I often put all the fields in details (and leave nothing on screen), use Bar chart (it's good because it allows me to see the data). Then I export it to mdb, then connect to it again. Unfortunately Tableau doesn't directly export to tde.

这篇关于Tableau中的数组计算，maxif例​​程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！