Sum Over DistinctCount with Different Agg Levels

Question & Answer
,
1

I am trying to calculate the cost of different job types over the total number of operations over a date range. For this, I have a date_granularity calculated field that is truncating the date part of the depending on the user choice date_parameter:

ifelse(
    ${granularity}='Quarter',truncDate('Q',{date}),
    ${granularity}='Month',truncDate('MM',{date}),
    ${granularity}='Week',truncDate('WK',{date}), 
    truncDate('DD',{date})
)

The cost field is calculated as follows:

sum(
    {job_cost},
    [{date_granularity}, {job_type}]
)

The distinct count uses the same calculated field for the partitions and is called num_operations_in_window:

distinct_count(
    {operation_id},
    [{date_granularity}]
)

When I try to divide the two fields:
{job_type_cost}/{num_operations_in_window}

I get the error:

The Level Aware Calculation (LAC) aggregate functions inside one visual aggregate functions should always share the same grouping key

Is there some workaround to this? I just want to divide those two fields (first one is job_type_cost and the second one is num_operations_in_window):
Screenshot 2023-10-10 at 11.17.01 AM

2

Hi @juan_camilo_puello , welcome to the QuickSight community!

I see your numerator that is {job_type_cost} has both {date_granularity}, {job_type} in the group by and only {date_granularity} for {num_operations_in_window} calculation or the denominator. Is that how you want your final metric to be calculated?

3

Hey @salim ! Yes, the requirement is to take the whole number of operations in that window even if the job was applied on less operations.

4

Hello @juan_camilo_puello and @salim!

@juan_camilo_puello are you still having trouble with this problem, or were you are able to find a solution? If you were able to find a solution could you post it to help the community?

One thought I have here is that since you just want to show all operations in the window of time to turn that lac function into an ifelse statement.

ifelse(
{date} <= {date_granularity} AND
{date} >= {date_granularity}, 
distinct_count({operation_id}), 
NULL)

You may need to experiment with this but this direction should get you around that LAC error.

1 Like
5

Hello @juan_camilo_puello and @salim !

It has been some time since there has been activity on this question, but we would still like to help find a solution. Are you still running into this problem or were you able to resolve this, and if so could you share the solution to help the community?

If we do not hear from you in 3 days this topic will be archived.