Filter data using IF Statement in Tableau

0
votes

I have a data source in tableau that looks something similar to this:

   SKU      Backup_Storage
  
   A        5
   A        1
   B        2
   B        3
   C        1
   D        0

I'd like to create a calculated field in tableau that performs a SUM calculation IF the SKU column contains the string 'A' or 'D' , and to perform an AVERAGE calculation if the SKU column contains the letters 'C' or 'B'

This is what I am doing:

IF CONTAINS(ATTR([SKU]),'A') or 
CONTAINS(ATTR([SKU]),'D') 
THEN SUM([Backup_Storage]) 
ELSEIF CONTAINS(ATTR([SKU]),'B') or 
CONTAINS(ATTR([SKU]),'C') 
THEN AVG([Backup_Storage])
END

UPDATE - desired output would be:

SKU    BACKUP
A, D   6               (This is the SUM OF A and D)
B, C   2               (This is the AVG of B and C)

The calculation above shows as valid, however, I see NULLS in my data source table. Any suggestion is appreciated.

I have named the calculated field: SKU_FILTER_CALCULATION

enter image description here

2
if-statementtableau-apivisualization
I see there is some mistake in desired visualisation. Can you include what you want as an output for the sample data included here, by typing or writing it? - AnilGoyal
Try to understand like this, you working at different row levels here, the results will be different for each row and you have already defined the aggregation for each row. But tableau isn't sure what to do with these different results for each row, means how to aggregate these further. Unless you are pretty clear what you are trying to get and at which level of detail, solution cannot be proposed. E.g. results of A and D rows can be added to results of B and C. These can be averaged, substracted to. Do you want to sum these individual results. A function cannot have multiple images - AnilGoyal
I will update the post . Thanks - Lynn

2 Answers

1
votes

Basically, IF THEN ELSE condition works when one test that is either TRUE/FALSE. Your specified condition is not a proper use case of IF THEN ELSE because SKUs can take all possible values. See it like this..

your data

SKU      Backup_Storage
  
   A        5
   A        1
   B        2
   B        3
   C        1
   D        0

Let's name your calc field as CF, then CF will take value A in first row and will output SUM(5) = 5. For second row it will output sum(1) = 1, for third and onward rows it will output as avg(2) = 2, avg(3) = 3, avg(1) and sum(0) respectively. all these values just equals [Backup_storage] only and I'm sure that this you're not trying to get.

If instead you are trying to get sum(5,1,0) + avg(2,3,1) (obviously i have assumed + here) which equals 8 i.e. one single value for whole dataset, please proceed with this calculated field..

SUM(IF CONTAINS([SKU], 'A') OR CONTAINS([SKU], 'D')
THEN [Backup storage] END)
+
AVG(IF CONTAINS([SKU], 'B') OR CONTAINS([SKU], 'C')
THEN [Backup storage] END)

This will return an 8 when put to view

enter image description here

Needless to say, if you want any other operator instead of + you have to change that in CF accordingly

1
votes

As per your edited post, I suggest a different methodology. Create diff groups where you want to perform different aggregations

Step-1 Create groups on SKU field. I have named this group as SKUG

enter image description here

Step-2 create a calculated field CF as

SUM(ZN(IF CONTAINS([SKU], 'A') OR CONTAINS([SKU], 'D')
THEN [Backup storage] END))
+
AVG(ZN(IF CONTAINS([SKU], 'B') OR CONTAINS([SKU], 'C')
THEN [Backup storage] END))

Step-3 get your desired view

enter image description here

Good Luck