设置差异：在Tableau Desktop中为两个组查找不同的成员 [英] Set difference: find distinct members for two groups in Tableau Desktop

问题描述

我如何在Tableau Desktop中实现以下设置差异/组区分任务？

How do I realize the following set difference/group distinction task in Tableau Desktop?

我是否已经对产品进行了调整（ adjusted = 1 或 adjusted = 0 ）。请注意，每个产品可能会被列出多次（因为实际数据集是每个产品的堆叠时间序列的矩阵）。

I have products who have either been adjusted or not (adjusted = 1 or adjusted= 0). Note that each product might be listed more than once (as the real dataset is a a matrix of stacked time series for each product).

我想找出至少有一项调整的产品有多少，而没有一项调整的产品。

这是在R中执行的操作：

This is how would do it in R:

示例数据：

dat <- data.frame(
  product = c("4005808588763", "4005808250004", "4005808157822",
    "4005800031052", "4005808855735", "4005808651818", "4005808322053",
    "4005808236879", "4005800091629", "4005808361434", "42277248",
    "4005808224074", "9005800249858", "42277835", "4005808627356",
    "8005800010985", "4005808323197", "4005808186129", "4005800059254",
    "4005808818587", "4005900175410", "72140018627", "4005800059292",
    "72140008499", "4005808125968", "42269847", "4005808675173",
    "72140016371", "4005808765157", "4005900123763", "4005808816019",
    "4005800062575", "4005808293872", "4005900143952", "8850029006536",
    "4005800136986", "42231493", "4005808715688", "4005800053085",
    "4005800059629", "4005808847419", "4005800031656", "4005900273994",
    "4005900261038", "6009661219022", "42240181", "8850029016030",
    "4005900146274", "42176152", "4005808158096"), 
  adjusted = c(1L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 1L, 0L, 1L, 1L, 0L, 0L, 0L, 0L, 0L,
      1L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 0L, 1L, 1L, 1L, 0L, 0L, 0L, 1L,
      0L, 1L, 1L, 1L, 1L, 0L, 0L, 0L, 1L, 1L, 1L, 1L, 1L, 1L, 1L, 0L,
      1L)
  )
#          product adjusted
# 1  4005808588763        1
# 2  4005808250004        1
# 3  4005808157822        0
# 4  4005800031052        1
# 5  4005808855735        0
# 6  4005808651818        1
# 7  4005808322053        1
# 8  4005808236879        0
# 9  4005800091629        1
# 10 4005808361434        0
# 11      42277248        1
# 12 4005808224074        1
# 13 9005800249858        0
# 14      42277835        0
# 15 4005808627356        0
# 16 8005800010985        0
# 17 4005808323197        0
# 18 4005808186129        1
# 19 4005800059254        0
# 20 4005808818587        0
# 21 4005900175410        1
# 22   72140018627        1
# 23 4005800059292        1
# 24   72140008499        1
# 25 4005808125968        1
# 26      42269847        0
# 27 4005808675173        1
# 28   72140016371        1
# 29 4005808765157        1
# 30 4005900123763        0
# 31 4005808816019        0
# 32 4005800062575        0
# 33 4005808293872        1
# 34 4005900143952        0
# 35 8850029006536        1
# 36 4005800136986        1
# 37      42231493        1
# 38 4005808715688        1
# 39 4005800053085        0
# 40 4005800059629        0
# 41 4005808847419        0
# 42 4005800031656        1
# 43 4005900273994        1
# 44 4005900261038        1
# 45 6009661219022        1
# 46      42240181        1
# 47 8850029016030        1
# 48 4005900146274        1
# 49      42176152        0
# 50 4005808158096        1

分为两个数据帧：

g1 <- filter(dat, adjusted == 0)
g2 <- filter(dat, adjusted == 1)

查找唯一的产品ID：

(id_1 <- unique(g2$product))
# [1] "4005808588763" "4005808250004" "4005800031052" "4005808651818" "4005808322053"
# [6] "4005800091629" "42277248"      "4005808224074" "4005808186129" "4005900175410"
# [11] "72140018627"   "4005800059292" "72140008499"   "4005808125968" "4005808675173"
# [16] "72140016371"   "4005808765157" "4005808293872" "8850029006536" "4005800136986"
# [21] "42231493"      "4005808715688" "4005800031656" "4005900273994" "4005900261038"
# [26] "6009661219022" "42240181"      "8850029016030" "4005900146274" "4005808158096"

(id_2 <- setdiff(unique(g1$product), id_1))
# [1] "4005808157822" "4005808855735" "4005808236879" "4005808361434" "9005800249858"
# [6] "42277835"      "4005808627356" "8005800010985" "4005808323197" "4005800059254"
# [11] "4005808818587" "42269847"      "4005900123763" "4005808816019" "4005800062575"
# [16] "4005900143952" "4005800053085" "4005800059629" "4005808847419" "42176152"

由于我对Tableau相当陌生，所以我并不是知道如何实现这样的查询。

As I'm pretty new to Tableau, I don't really know how to go about implementing such a query.

推荐答案

Tableau中至少有两个功能可用于解决此类问题：计算集和LOD计算。也有其他可能性。

There are at least two features in Tableau that are useful for problems like this: computed sets and LOD calculations. There are other possibilities too.

这里是一种使用基于Product_ID字段的（计算）集来指示哪些产品至少进行了一次价格调整的方法。选择Product_ID字段，右键单击并创建一个集合。在常规选项卡上选择全部使用选项，然后切换到条件选项卡。然后选择按字段，将字段调整为并将条件设置为SUM（）>0。用SQL术语来说，您的新集包含HAVING SUM（调整后）> 0的那些Product_ID。

Here is an approach using a (computed) set based on the Product_ID field to indicate which products have had at least one price adjustment. Select the Product_ID field, right click and create a set. Choose the "Use All" option on the General tab, and then switch to the Condition tab. Then select "By field", the field "adjusted" and set the condition to SUM() > 0. In SQL terms, your new set contains those Product_IDs HAVING SUM(adjusted) > 0.

然后可以将新集合放在行架子上以显示IN / OUT，然后将COUNT DISTINCT（Product_ID）放在列架子上以显示集合中有多少产品，没有多少产品。

You can then put your new set on the row shelf to show IN/OUT, and then place COUNT DISTINCT(Product_ID) on the column shelf to show how many products are in the set and how many products are not.

这篇关于设置差异：在Tableau Desktop中为两个组查找不同的成员的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！