Hi QuickSight Experts -
Can someone please help me calculate median of sales_amt in a year/week (ignoring duplicate entries for each sales_id and sales_amt.
.
I tried Notion β The all-in-one workspace for your notes, tasks, wikis, and databases. but it giving me unexpected result, not sure if i am missing something here. Below is unexpected result and calculated field logic - median outputs 1,1 instead of 125, 125
Calculated_Fields
Distinct Count ==== distinctCountOver({sales_amt},[{sales_id}],PRE_FILTER)
Ifelse Rank ====
ifelse( rank([{sales_amt} ASC], [{sales_id}], PRE_AGG) = 1, {Distinct Count}, NULL )
neelay
April 24, 2023, 3:50am
2
@bipshik
if your data look exactly like this, QS will remove duplicates and it will look like this
Do you want to display 125 (median) in a new column(after the amount)?
if you convert year+week into date then you might be able to use periodToDateMedian
sorry unfortunately this function doesnβt fulfill my requirement. Though thanks for the help @neelay
I was able to resolve this myself.
posting here steps
Step 1 - Derived median of sales_amt partition by sales_id, lets call sales_median_by_id == median({sales_amt},[{sales_id}])
Step 2 - On visual, took median of sales_median_by_id
1 Like