Quick response please-Taking average of summed value (LAC A/LAC W)

[Awan]

This is my calculate field. The logic is working fine with LAC A function. For example

and for a particular period i have checked the mean calculated field with use of LAC A function it was giving me the correct result. The below result is by using LAC A function

For some reason i need this calculation to work by using LAC W function. But i used the LAC W calculated field as shown above but I’m getting different result.

FYI this is the LAC A calculated field

I need a quick response on this. Thank you

[Brett]

Hi @Awan,
If your calculation is working with LAC-A, what is the reasoning for having to use LAC-W? When using LAC-A vs. LAC-W, the order of calculation will slightly differ which is where you may encounter a slight variance (like the one you’ve received).
Have you tested separately to confirm which mean is more accurate?

A couple things you could potentially try:

• Remove the ‘avgOver’ aggregation and instead apply the average aggregation at the field well level.

• Try using ‘truncdate’ to day for your order date; while your timestamps all look to be the same in the example, if by chance there’s a small variance in the time, it may not include.

• Try just using a regular ‘avg’ calculation instead of ‘avgOver’

• Try switching the PRE_AGG to PRE_FILTER to see how that alters the outcome.

Let me know if any of these suggestions help at all or if you have any additional questions. I’ll include an article below that has helpful hints when working with LAC-A and LAC-W functions.

Level-Aware Calculations: Tips and Best Practices

[Level-Aware Calculations in Amazon QuickSight] Level-Aware Calculations (LAC) are powerful functions that allow us to perform calculations at a granularity level which is different from the granularity of our datasets and/or visuals. The sample dataset referenced by this article is a dataset of product orders placed by customers. Each order can contain multiple products and each product in an order has a unique Line Item ID. Each row of our dataset represents one line item but we want to per…

[Awan]

Hi @Brett
I’m not using this on field wells. I’m using this in another calculated field.
Here is a detailed situation,

So lets go with LAC A

so i have shown you how I’m calculating “mean”

now i need to calculate standard deviation : “std dev”
stdevp(sum({outb_final_quantity_requested},[{order_date}])/distinct_count({LAG_VALUE},[{order_date}]))

Now i need to calculate lower bound and upper bound.
“Lower bound” : mean-2std dev
“Upper bound” : mean+2std dev

now i need to check which and all are outlier, so i need to group it on order date for any selection and compare the actual value with lower and upper bound

“actual value for outlier”: sum({outb_final_quantity_requested},[{order_date}])/distinct_count({LAG_VALUE},[{order_date}])

Now im creating a flag whichever is the outlier I’m putting 1. But with LAC A below is the error

This is the error you can suggest the changes which will remove this error.

One reason to use LAC-W was this and for some reason LAC A function i cannot use in visual.

[Brett]

Hi @Awan,
Thanks for the additional information. I’d be curious to know when you tested out the suggestions above, did you see the value you were expecting?

In regards to your mismatched aggregation; the problem is likely due to the upper and/or lower bound calculations. That error basically means that one of your fields needs to be aggregated so they can be used together in a calculation. An example:

Returns aggregation error: ifelse(sum({Sales}) > 1000, {Product}, NULL)
Doesn’t return error: ifelse({Sales} > 1000, {Product}, NULL)
Also doesn’t: ifelse(sumOver({Sales}, [{Customer}], PRE_AGG) > 1000, {Product}, NULL)

Since you are utilizing an aggregation in the actual value for outlier calculation, I believe the issue is with the other 2 fields you are using. While it may look messy in the calculated field, it may be easier to see what is causing the error if you utilize the full calculations in the Outlier Flag calculation rather than a bunch of calculated fields.

Additionally, it may be easier to assist further if you’re able to upload a sample of your analysis to the QuickSight Arena view with anonymized data.

[Awan]

yes i understand that. But can you tell me the change in calculated field which will yield me the correct result without tampering it. I have used LAC W for another scenario where we don’t have lag concept that means our data wont repeat.

The scenario without the lag:
mean :

standard dev:

then the lower bound=mean-2*(std dev)
upper bound =mean+2*(std dev)

actual value:

outlier flag:

This is working fine

I need to replicate this for another scenario where i have lag
which basically means
for a particular combination for a date it will repeat by number of times in lag.
lag1,2,3 due to forecast. but the actual values will also repaeat.
which is why im using this

but this is not giving me the correct result.

I have explained it in detailed version, if any of the Quicksight expert can help me with calculated field why is it calculating wrong and how can i change the calculated field. Use these fields and take this example and im hoping a quick response and a response where im getting help in calculated field.

[Brett]

Hi @Awan,
In your LAC A calc. field you used distinct count of ‘LAG String’ whereas in your updated formula you’re doing a distinctcountover of ‘outb_final_quantity_requested’. Judging by your original picture showing data, your final qt values are the same, so a distinctcount of that will be 1.

[Awan]

for sumOver i cannot use distinct_count i should be using distinctcountOver(). Are you saying that distinctcountOver will return 1?

[Brett]

Hi @Awan,
I’m more so referring to what your formula is taking a distinctcountover of: ‘outb_final_quantity_requested’, does that field have no connection to your first screenshot where you’re showing the data? I was under the impression that was referring to your ‘final qt’, where the values are the same. Taking a distinct count of those values will bring back 1 because they field values are the same.

If my assumption was wrong, please let me know what ‘outb_final_quantity_requested’ is (and if it’s a calculated field, please share that).

[Brett]

Hi @Awan,
It’s been awhile since we last heard from you, did you have any additional questions regarding your post?

If we do not hear back within the next 3 business days, I’ll go ahead and close out this topic.

Thank you!

[Brett]

Hi @Awan,
Since we haven’t heard back, I’ll close out this topic. However, if you have any additional questions, feel free to create a new post in the community and link this discussion for relevant information if needed.

Thank you!