CODEWITHSUNDEEP
tableau-apiaggregationrolling-computation
18Man
18Man

Reputation: 572

How to aggregate table calculation in tableau

this is my workbook

enter image description here

on that workbook, i calculate timediff between each transaction for each users. what i build first is the filters PUL with 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 to find out the users who match with conditions (do transaction at least 2 before end_date parameter, and atleast doing 1 transaction in between start_date parameter and end_date parameter) after that i add to context this filters to find out the users first.

and i made filters date_range with this calculate

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

so it will visualize only transaction on range (start_date as first range, and end_date as last range) and also visualize last date transaction before first range (if any).

after that i make calculate called datediff DATEDIFF('day',LOOKUP(MIN([Created At]),-1), MIN([Created At])) and put that on label so it will calculate the day different. and also i put the date in the detail and put the date also in rows and make it ATTR.

my question is, how to find out max, min, median, and average value from this calculate

enter image description here

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

Upvotes: 2

Views: 1115

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

For Max and Min you can proceed like I presented you a solution on your previous question. (For max create a rank calculation and sort is descending, For Min you create a second rank calculation ordered ascending).

However, as far as my knowledge of table calculations in tableau goes, Tableau doesn't allow to hard-code these table calculated fields and therefore you cannot-

  • further aggregate these results
  • perform LOD calculations on these.

For calculation of these like average and median, It is advised that you may please create a hard-coded column/field which give you the time-difference on any order with that customer's previous order. This you can do it in any programming language of your choice like R or python (or other).

Moreover, Tableau integration with R/python is through script-real type functions which are again of table calculation category and above restrictions will apply.

Good Luck.

EDIT as Alex Blakemore has suggested on a different question/answer, you can use window functions with a slight tweak. Let's assume your calculated field name for datediff is [CF], then create four calculated fields with the following calculations.

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

enter image description here

  • thereafter 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 as desired.

enter image description here

Upvotes: 3

Related Questions