I have the following 2 calculations:
distinct_count(Location,[{Site Code}]) - location codes partitioned by site code
distinct_count({Inspection Date}) - unique inspection dates
x = distinct_count(Location,[{Site Code}]) * distinct_count({Inspection Date})
I need to multiply these 2 functions to derive a new calculation. Unfortunately QS is displaying an error.
Appreciate any insights on a workaround solving this problem. Thanks
Hello @SureshB, thanks for your question.
Assuming you’re attempting to calculate the product of the distinct count of locations partitioned by site code and the distinct count of inspection dates, you might need to use an aggregation function like sum or avg to ensure compatibility. Here’s a possible approach:
sum(distinct_count(Location,[{Site Code}])) * sum(distinct_count({Inspection Date}))
By using the sum function, you aggregate the results of the distinct count calculations, allowing you to multiply them together in a new calculated field.
I hope this helps
Thanks @mzerilli for your help.
I did try that and it is still throwing this error - Nesting of aggregate functions is not allowed.
Thanks for getting back @SureshB !
Did you try referencing the variables created through calculated fields?
For example assuming
{DistinctCountLocations}= distinct_count(Location,[{Site Code}])
and
{DistinctCountInspectionDates}= distinct_count({Inspection Date})
Defining a new calculated field that multiplies the above.
Let me know if this works
This is the error message I am getting:
The calculation operated on Level Aware Calculation(LAC) agg expressions is not valid
Alright.
It would be needed to use Level-aware calculation - window (LAC-W) functions, in particular by using distinctCountOver instead of the distinct_count.
Here I leave the links for the documentation, please let me know if this helps
Hello @SureshB !
Was @mzerilli 's suggestion helpful and if so could you mark their comment as a solution to help the community?
It has been some time since we have heard from you but we would still like to help you find a solution. If we do not hear from you in the next 3 business days this post will be archived.
Hi
This issue is not resolved yet and still need a solution. I tried several things as suggested by @mzerilli, but not working so far. Possible her workarounds may need some tweaks to make it click. Look forward to hearing back from you guys as this is a critical metric needed to improve operational improvement
Hello @SureshB, if you want to have a value display where you are counting locations by site code and then for each site code taking that total and multiplying the distinct count of total inspection dates, it would look something like this:
x = distinctCountOver(Location,[{Site Code}], PRE_AGG) * distinctCountOver({Inspection Date}, [], PRE_AGG)
The first distinctCountOver will give you a different value depending on the Site Code whereas the 2nd function will give you the same value on all rows since it takes the total dataset to calculate.
Hi Suresh,
I tried similar calculation over formula and it worked. One thing to consider that in your formula [distinct_count({Inspection Date})] calculations is computed at table visual level, so other fields selected in table (in my example below product, country) also plays role in computation of distinct_count({Order Date}). Otherwise you have to use LAC Windowing function Using level-aware calculations in Amazon QuickSight - Amazon QuickSight
OrderCountperCountry=distinct_count({Order ID},[Country])
CountDistinctOrderDate=distinct_count({Order Date})
Multiply=min(OrderCountperCountry)*CountDistinctOrderDate
Thanks @SureshB for circling back to let us know.
Thanks @AnwarAli. Will try your solution as well to see if that works.
