LAC Calculation

Weekly Average of Customer: { EXCLUDE[create_date]: AVG({Include[Customer_name], week([create_date]):COUNTD([id)})}

Hi I have this LOD function in tableau which I have been trying to convert to QuickSight however I’m unable to achieve the desired result. Could you please help in converting this to Quicksight equivalent?

1 Like

Hello @samytar97, what are you wanting the function to calculate? I am unfamiliar with Tableau calculations so I want to make sure I completely understand your expected output. I should be able to guide you with a little more information. Thank you!

This formula is designed to calculate the average number of unique alerts received by each customor per week. So, for each customor, the formula calculates the weekly count of their unique alerts and then averages those weekly counts to arrive at a single value - the average number of unique alerts received per week for that customor.

In essence, this formula helps understand the customor weekly average
@DylanM

Hello @samytar97, thank you for the explanation. We will use nested LAC-W aggregations to get the weekly average per client. I’ll link documentation about them as well, but you can use a distinctCountOver to get the number of distinct IDs per client and per week, then wrap that in an avgOver to receive the weekly average by client.

Weekly Average =

avgOver(
distinctCountOver({ID}, [{Customer_name}, {week}], PRE_AGG),
[{Customer_name}], PRE_AGG
)

If your date field is not already aggregated into a week date value, I would handle the truncDate calculation in a different field and bring it into the partition above. When nesting the LAC-W aggregations, QuickSight will throw an error if you truncate the date in one partition but not the other.

Week = truncDate('WK', {Date})

This should get provide you with the result you are looking for. I will mark this as the solution, but please let me know if you have any further questions. Thank you!