Execute multiple groupby transformations and aggregate functions within Tableau

1
votes

I have a dataset that looks very similar to the data below.

  1. I would like to create two groups using values in the group column.

    group1 - new &

    group2 - old

  2. Within the group1 - new, I would like to then groupby id and take the average of each grouped id and then sum

data:

     id     type    backup  free    total   Last refreshed
     a      old     1       4       5       11/1/2020
     b      old     1       4       5       11/1/2020
     c      old     1       4       5       11/1/2020
     d      new     2       1       3       11/1/2020
     e      new     2       1       3       11/1/2020
     f      old     1       1       2       11/1/2020
     g      old     1       1       2       11/1/2020
     e      new     2       2       4       11/1/2020
     d      new     2       2       4       11/1/2020

1st step - create group1 and group2

    id  group   type    used free   total   Last refreshed
    a   group2  old     1    4      5       11/1/2020
    b   group2  old     1    4      5       11/1/2020
    c   group2  old     1    4      5       11/1/2020
    f   group2  old     1    1      2       11/1/2020
    g   group2  old     1    1      2       11/1/2020
    d   group1  new     2    1      3       11/1/2020
    e   group1  new     2    1      3       11/1/2020
    e   group1  new     2    2      4       11/1/2020
    d   group1  new     2    2      4       11/1/2020

2nd step groupby id

id  group   type    used    free    total       Last refreshed
   d    new     group1  2       1       3       11/1/2020
   d    new     group1  2       2       4       11/1/2020
   e    new     group1  2       1       3       11/1/2020
   e    new     group1  2       2       4       11/1/2020
   a    old     group2  1       4       5       11/1/2020
   b    old     group2  1       4       5       11/1/2020
   c    old     group2  1       4       5       11/1/2020
   f    old     group2  1       1       2       11/1/2020
   g    old     group2  1       1       2       11/1/2020

3rd step we groupby id and take the average of d (for each column) we groupby id and take the average of e (for each column) then we sum the values in the old group (for each column) then we sum these altogether

  id    used    free    total   Last refreshed
  d     2       1.5     3.5     11/1/2020
  e     2       1.5     3.5     11/1/2020
  old   5       14      19      11/1/2020

Desired Result

All summed together to get:

 used   free    total   Last refreshed
 9      17      26      11/1/2020

graph:

enter image description here

This is what I am doing:

2.place the type in groups 2.create calculated field

zn(avg(if [Group]= 'Group1' end))
+
zn(sum(if [Group] = 'Group2' end))

enter image description here

any suggestion is appreciated

1
if-statementtableau-apivisualization
In your group2 all the ids are different, no two are same. So if you take average of these single rows (in group2) the result will be same. Moreover, as suggested earlier you have to pivot the columns to show the measure values together. What I am trying to say is that (i) if the ids are group are distinct the problem can be solved much easily, (ii) With your desired result shown above, you cannot achieve the visual (simply because in your result there are three measures while in chart only one) - AnilGoyal
@AnilGoyal the values in group2 are only to be summed. The measures are used, avail and total - Lynn
Isn’t there a way to create two groups: group1 and group2 and then to group by the values within one of these groups and take average and the other the sum? I am still working on this - Lynn
@Lynnette could your sample data ever have more than one row for an "old" id, say two data rows with id="a'"? if so, would you first group by id and average the values (before the final summation step) like you do with new ids? - Alex Blakemore
Exactly Alex! that's what I was also trying to ask Lynnette. If all Old ids have one row per date, the problem is much simpler. see this screenshot - AnilGoyal

1 Answers

1
votes

For the first part i.e. groupby transformations and aggregations please create three calculated fields with the following calculations

  1. desired free
{SUM({Fixed [Last Refreshed]: SUM(IF [Type] = "new" THEN
{FIXED [Last Refreshed], [Type], [Id] : AVG([Free])}
ELSEIF [Type] = "old"
THEN {FIXED [Last Refreshed], [Type], [Id] : SUM([Free])}
END)})}

similarly for desired used and desired total. You'll get a aggregated table as desired (see screenshot)

enter image description here

For creating a bar chart (stacked) you'll have to pivot (transform) the data by clubbing used and free into one dimension. If you want I can again present you a solution.