how to display all of the MIN, MAX, AVERAGE, AND MEDIAN from the results

0
votes

i have a table like this

order_id | user_id | createdAt | transaction_amount

order_id as the id of the transaction, user_id as the user, createdAt as the dates, and transaction_amount is the transaction of each id order.

so this is the continue from this question how to display max, min, median, and average from tableau

enter image description here

on that picture, that is user_id and the right side are the frequency of transaction for each users. there's 3139 data.

so based on that solution i've made max min average and median from window calculation like this

for max WINDOW_MAX(COUNTD([Order Id]), FIRST(), LAST()) min WINDOW_MIN(COUNTD([Order Id]), FIRST(), LAST()) avg WINDOW_AVERAGE(COUNTD([Order Id]), FIRST(), LAST()) median WINDOW_MEDIAN(COUNTD([Order Id]), FIRST(), LAST())

so i put results for calculation on columns and rows are users_id enter image description here

what i want is, how to convert that numbers into like this enter image description here

so the min value(1), max value(12), average value (1.72), and median(1) value are only appears once, not appear many and confusing.

1
tableau-api
Can you please provide me a sample data/sheet? I'll show you in a moment – AnilGoyal
ok wait let me edited it – 18Man
its done, i put the structure of my table – 18Man
okay, basically i want to put median value, min, max, and average value from the users who match with the filter that has been made – 18Man
The filters which you want to be calculated prior to calculation of these LODs, the same should be added to context by right clicking these. Else your filters will be applied after calculation of Max/Min etc. – AnilGoyal

1 Answers

1
votes

Here is my proposed solution. Since window functions work in specific context, I propose use of LOD for these calculations.(Context is already FIXED in all LOD calculations and therefore these are independent of sheet's view context)

Step-1 Make four Calculated fields as

  1. Max Frequency as
Max({FIXED [User]:COUNTD([order_id])})
  1. 'Min Frequency` as
Min({FIXED [User]:COUNTD([order_id])})
  1. Average Freq as
AVG({FIXED [User]:COUNTD([order_id])})
  1. Median Frequency as
MEDIAN({FIXED [User]:COUNTD([order_id])})

Add all four to crosstab view as desired. Sample data used-

user    trans date  order_id
user1   02-12-2019  1
user1   02-01-2020  2
user2   03-01-2020  3
user3   03-12-2019  4
user3   04-12-2019  5
user4   01-02-2020  6
user4   02-02-2020  7
user5   02-01-2020  8
user5   03-01-2020  9
user1   03-02-2020  10
user1   03-03-2020  11
user3   03-01-2020  12
user3   03-02-2020  13
user1   04-02-2020  14

Desired view screenshot

enter image description here

I think this solves your problem. Good luck.