8 Replies Latest reply on Aug 14, 2019 3:35 PM by John Sarantos

# what's the difference of max[date] and datediff('day',[date],{max[date])}=0

I create calculated field "max date" using code: max[date].

I create calculated field "last day" using code: datediff('day',[date], [max date])=0

I try to use sum(IIF([last day], [volume],null) or sum(IIF([max date], [volume], null) to calculate last day volume.

I don't know why the second doesn't work. (volume is one of the measures.)

I try to calculate the last week using code: datediff('day',[date],[max date])<=6; here, I can't use [last day].

For me, the [max date] is the same to [last day]. Is there difference between max[date] and {max[date]}? I couldn't really understand the differences. I googled and it says the former is aggerated and the latter is not.

They are very confusing.

Thanks,

• ###### 1. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Hello

I see you wrote a lot of questions but I wasn't able to understand what you need to calculate.

Maybe if you describe what you need (more than trying to clarify what is the difference in the calcs) we can help you to get what you want. Maybe none of the calculations you have now are the solution for it.

Keep vizzing
Rodrigo

• ###### 2. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Hi Jing,

max(date) - will give the max value based on the granularity you use in the sheet

whereas

{max(date)} - will give the max date in the data set irrespective of data granularity.

max[date]

to fetch the last day

datediff('day',min([date]), [max date])=0

IIF Calculation to use

IIF([last day],SUM( [volume]),null)

Hope this will helps

BR,

NB

• ###### 3. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Hi Jingchun,

Here is a visual explanation of the difference:

{Max([Order Date])} is a Table-Scoped Level of Detail expression.  This means it is going to return the Max Order Date from the entire table in the view.

MAX([Order Date]) will find the Max([Order Date]) at the level of the Dimensions in the view, so it's finding the Max([Order Date]) for whatever row it's on.

Here's another visual:

Notice that {Max([Order Date])} is still returning the same value, whereas MAX([Order Date]) is show many different values, based on what is the Max([Order Date]) based on the combination of Dimensions in the view.

I've attached the workbook I used to take these screenshots for reference.

• ###### 4. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Hi, Jingchun

The main difference between these two expression is one is normal aggregation function, while the other one with {} is actually an LOD (Level of Detail Expressions - Tableau ), if you are familiar with SQL, it kind of "over partition by" expression.

Below is an example of explaining a bit more about the difference

Hope this makes sense.

ZZ

• ###### 5. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Thanks. I got it.

Your explanation is very easy to follow.

• ###### 6. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

I don't have any programming experience. This is totally new to me.

• ###### 7. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Great! I'm glad it helped!  Can you mark as correct answer to close the post?

Thank you,

John

• ###### 8. Re: what's the difference of max[date] and datediff('day',[date],{max[date])}=0

Hi Jingchun,

Is it possible to mark the post of mine from earlier that answered your question as correct answer so that it will appear at the top when people search for an answer to this question?

Thank you,

John