2 Replies Latest reply on Sep 5, 2017 4:31 AM by Emanuele Marcandella

# Aggregation mindset issue

Hello everyone,

I'm angry with Tableau right now because I can't do something that in my mind appear very easy and also in excel appear easy as well.

I think that I have a mindset issue and I hope some of you can help to clarify the theory about WHY I can't do some so easy in my mind.

I have a table with 2 columns both in format Date.

I need to verify if the value in the column 2 is higher that the minimum of the column 1. Then sum the number of cells that are true.

WHAT I DO EASILY IN EXCEL

I have a lot of way to do that in excel, COUNTIF for exaple, but I go with the dumbest way as if I'm using Tableau.

I create a column "Result" that give me the result of IF and then sum.

Result: =IF(B2<MIN(A2:A146);1;0)

Sum: =SUM(C:C)

(I attach the file even if it's not so useful)

EASY

WHAT I CAN'T DO EASILY IN TABLEAU

If I use in tableau:  IF [Date 2]<MIN([Date 1]) THEN 1 ELSE 0 END

It's of course a mistake because I cannot mix aggregate and not-aggregate arguments but... WHY?

And even if I use ATTR for date 2, then my calculation is aggregated and I can't SUM because I can't SUM that is aggregated.

W(hy)TF?

In particular I very often have the same issue with aggregation/non-aggregation issue expectially because if I use a value from another data source this will be usable only with ATTR.

I created a variable that is the first day of the last suitable week in a specific data source that MUST be used transversely in all other data sources.

I do the same with the last dat of the last suitable week.

I "just" need to check if, in another data source a date is between these two dates but, as I said, if I use them I can use them only inside ATTR().

I would like to know:

1) a solution to my issue

2) why it's so difficult do something easy. Is there a reason?

• ###### 1. Re: Aggregation mindset issue

Hi,

Without an attached workbook its hard to know what has gone wrong. I created an excel file based on your picture and then used that as my source in Tableau. Below are the steps I followed.

1) Convert the string dimensions to date dimensions. I think this is where you might have gone wrong since you can't work with string dimensions that way.

2) Create a calculated field: IF [Date 1] > [Date 2] then 1 else 0 END

3) Line them up next to each other and add a column grand total

Let me know if you have any questions!

/Alex

• ###### 2. Re: Aggregation mindset issue

The calculation field should be IF [Date 2] < MIN([Date 1]) ... because I need that every cell verify if it's less then the minimum of the date 1.

I attached the easy resolution in Excel

1 of 1 people found this helpful