CODEWITHSUNDEEP
tableau-api
18Man
18Man

Reputation: 572

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

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.

Upvotes: 0

Views: 1130

Answers (1)

AnilGoyal
AnilGoyal

Reputation: 26238

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.

Upvotes: 1

Related Questions