The first section is looking to find a field with the max date in the data set. As it's a table calc it depends on how the data is partitioned, but i would guess that it is in view with the dates as a dimension, so that the attr([date]) shows the date for that day, otherwise it would error with more than one option.
The reason the iif statement doesn't look to be checking against anything is that [current] is a boolean field, if the date is the max then it will be true, otherwise it will be false, so it is performing the calculation in the [current] field within that statement.
This is then returning the total of sales for the max date in the view.
see attached simple worksheet.... trying to understand the datediff of the order date and the total of max order date...
seems all 0 ?
if i've used Year, then those order dates in 2015 should be 3 right ? why does it show 0 ?
datediff.twbx 2.4 MB
It's because the table calculations for your total max order date and datediff fields are at different levels.
The date diff is set to be at table(across) level, so is only picking up the max date for each row, which is the order date. If you change this to table(down) then you get the 3 years you are expecting.
In this screenshot I've switched the table calc setting around.
Yes it works. But i dont get it. In my original file, for example Aaron Bergman, order date 18 Feb 2015, so what did it minus to get 0 for datediff ? did it subtract itself ?
As for total Max, what went wrong?
I think i get it but yet i dont... could you elaborate what went wrong with with the datediff and total max date table calc ?
1 of 1 people found this helpful
The way the field is set up is only calculating the max date from that row, table functions can calculate on different ranges, from just the active row to the entire table.
This should give you an overview of table functions