How to aggregate table calculation in tableau

Question

My workbook looks like this:

I compute the timediff between each transaction for each user in that spreadsheet. The PUL filters are the first thing I made using this calculate.

{Fixed [User Id]: sum(
if [Created At]<=[END_DATE] then 1 else 0 end)}>=2
AND
{FIXED [User Id]: sum(
IF [Created At]<=[END_DATE] AND 
[Created At] >= [START_DATE] THEN 1 ELSE 0 END)}>=1

This formula is used to identify users who meet the criteria (do at least two transactions before the end date parameter and at least one transaction between the start date parameter and the end date parameter). Then I add these filters to the context to find out who the users are initially.

I also used this compute to create date range filters.

lookup(min(([Created At])),0) >= [START_DATE] and 
lookup(min(([Created At])),0) <= [END_DATE]

As a result, it will display only transactions within a range (start date as the first range and end date as the final range), as well as last-date transactions prior to the first range (if any).

After that, I build a DATEDIFF('day',LOOKUP(MIN([Created At]),-1), MIN([Created At]) and place it on a label so that it calculates the day differently. I also placed the date in the detail, as well as in the rows, and made it ATTR.

How can I get the maximum, minimum, median, and average values from this calculator?

i tried with calculated max

MAX({FIXED [User Id]:DATEDIFF('day',INT(LOOKUP(MIN([Created At]),-1)), INT(MIN([Created

 At])))})

but it return error datediff being called with string,integer,integer

Answer 1

You may proceed with Max and Min in the same way that I did with your prior inquiry. (For max, establish a rank calculation with descending sorting; for min, build a second rank calculation with ascending sorting.)

However, as far as my understanding of Tableau table calculations goes, Tableau does not enable you to hard-code certain table computed variables, therefore you won't be able to-

LOD computations are performed on these findings once they have been aggregated.

It is recommended that you build a hard-coded column/field that gives you the time-difference on any order with that customer's prior order for calculations such as average and median. You may perform this in whatever programming language you like, such as R or Python (or other).

Furthermore, Tableau connection with R/python is accomplished using script-real type functions, which fall within the table calculation category and are subject to the same constraints.

EDIT: You may utilise window functions with a minor change, as Alex Blakemore mentioned on a previous question/answer. Create four calculated fields with the following calculations, assuming your calculated field name for datediff is [CF].

window_max([CF])

window_min([CF])

window_avg([CF])

window_median([CF])

and name them [CF max], [CF Min], [CF avg], [CF Median] respectively.

Now edit table calculation with nesting in each four these, as follows-

• click nested calculations down arrow. CF will be listed there. change its calculation to specific dimensions, at level deepest and restarting at evrey user id. the screenshot is

• then click nested calculations down arrow again. select CF_max/min/med/avg (as the case may be) and create table calculations with table down.

You'll get a view like this