9 Replies Latest reply on Mar 14, 2018 2:04 PM by Deepak Rai

# Average Multiple Samples into weighting of 1 sample per user

Hi all,

Long time reader. Was wondering if someone could help me with this. I have a dataset that has users that generate samples in a given day, and what I'd like to do is take all the samples of that unique user, average them, and treat them as 1 sample, for the time period specified. I would do that for multiple users, so in the end, regardless of how many tests a user performed, that particular user would have an average sample rating of 1, for the defined time (day, week, month, year, etc).

For example,

 Date User 1 User 2 User 3 01-Jan 8 78 02-Jan 41 50 96 03-Jan 99 04-Jan 84 15 38 05-Jan 63 06-Jan 96 86 07-Jan 55 54

Average Per Week

 Average Per Week User 1 Average Per Week User 2 Average Per Week User 3 Required Total Weekly Average (all unique user samples treated as 1 sample) (73.66+52.166+65.8)/3 73.66666667 52.16666667 65.8 63.87777778

Is this possible?

• ###### 1. Re: Average Multiple Samples into weighting of 1 sample per user

From your data you should get this value,

• ###### 2. Re: Average Multiple Samples into weighting of 1 sample per user

Hi Deepak,

That's actually the value i do not want, because it's treating every sample with equal weighting. What I was requesting was every user with equal weighting.

• ###### 3. Re: Average Multiple Samples into weighting of 1 sample per user

Can you explain how r u getting 73.66, 52.16 and 65.8

• ###### 4. Re: Average Multiple Samples into weighting of 1 sample per user

Sorry Deepak I just noticed that my copy/paste shifted my numbers over to the left in the original table..Let me try that again.

 Date User 1 User 2 User 3 01-Jan 8 78 02-Jan 41 50 96 03-Jan 99 04-Jan 84 15 38 05-Jan 63 06-Jan 96 86 07-Jan 55 54

User 1: (41+84+96)/3 = 73.66

User 2: (8+50+99+15+86+55)/6 = 52.16

User 3: (78+96+38+63+54)/5 = 65.8

• ###### 5. Re: Average Multiple Samples into weighting of 1 sample per user

Hi Bob,

First you have to Pivot your data and then I wrote an LOD to get this:

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread, The CORRECT button is in main thread, not in email

• ###### 6. Re: Average Multiple Samples into weighting of 1 sample per user

Thanks for this Deepak. I was exploring the fixed option but it doesn't make things flexible.

For example, I would need to apply filters and different time aggregates (weeks/month/year) to this method, and regardless of what I do, fixed would just keep reporting back the same value. Is there a way to make this more flexible?

• ###### 7. Re: Average Multiple Samples into weighting of 1 sample per user

Yes You can Do that, It is not Rigid, It is just a different way of calculations. You can read more about them here. Since FIXED are first expressions to get evaluated, you need to add your filters to CONTEXT  to achieve Filtering

In your Example, I added Date to Context Filter and See values changing. If you don't want to use FIXED you way use INCLUDE which is executed at Lower Level of Hierarchy

Top 15 Tableau LOD Expressions (Practical Examples)

Thanks

Deepak

If it Helps, Pl mark it Helpful and CORRECT to Close Thread, The CORRECT button is in main thread, not in email

1 of 1 people found this helpful
• ###### 8. Re: Average Multiple Samples into weighting of 1 sample per user

Deepak, thank you, once again. Adding filters into context helped quite a bit!

Hopefully you can help me out with the time aggregation as well. The example showed just 7 days, but i have years worth of data, and i would need this method applied on the date of aggregation chosen.

For example, if choose to aggregate by month, I would like to have this method applied by month. So for every given user within that month, average all their samples, and treat it as 1 sample for that user, per month. This is being plotted on a time graph which would show year over year, or month over month, week over week, etc. The level of aggregation is chosen by the user via parameter. (so for month it would be datetrunc('month', [test_date], week would be datetrunc('week', [test_date]), etc.

At the moment, I have the same value for every single year.

• ###### 9. Re: Average Multiple Samples into weighting of 1 sample per user

In that case you need to Create a parameter and that parameter can be used inside the FIXED Calc.

I think you can Start up a Separate Thread on that for clarity with New Data after marking my previous reply as CORRECT to close this one, so that others can refer this in future.

Thanks

Deepak