The level Aware Calculation (LAC) Is Not Supported in this Calculation at this moment

Question & Answer
, ,
1

I have a Level Aware Calculation (LAC) in my QuickSight Dashboard as follows:

%Constraint = {Processed Trailers}/{Max Appointment}

Definitions:

Processed Trailers = distinct_countIf({inbound_ship_appointment_id},{anchor_date} = {checkin_shift_date})

Max Appointment = SUM (MAX ({max_appointment_constraint},[{warehouse_id},{appointment_type},{anchor_date},{Shift Name}]))

My Dataset is a merger of two datasets.

  1. Trailer Level Data [PK: inbound_ship_appointment_id, anchor_date ]
  2. Shift Constraint table [PK: warehouse_id, anchor_date, shift_name, appointment_type]

The purpose of the %Constraint metric is to compare the total number of trailers processed by each warehouse (by date, shift, and appointment type) with the maximum appointments enabled in my constraint table for each site, date, shift, and appointment type.

My %Constraint metric works correctly and displays accurate percentages. However, I cannot sort my visuals by this column to see the top/bottom warehouses in terms of %Constraint. When attempting to sort my pivot table by %Constraint, I receive the following error:

The Level Aware Calculation (LAC) aggregate expressions is not supported in this calculation at this moment. Please remove the LAC aggregation expression from metrics.

I also want to analyze this data in heatmaps and bar charts but face the same sorting issue. Any assistance with this problem would be greatly appreciated.

1 Like
2

Hello @nebadi, welcome to the QuickSight community!

We can definitely make some updates to the calculated fields and hopefully fix some of the formatting/display issues in the visuals. The one thing I will point out though, is there are definitely limitations to the types of sorting that will be allowed in pivot tables. Even when it displays the option to sort based on the values (not columns or rows), it doesn’t actually change the display. If you want to be able to sort by the values field, I would recommend utilizing a standard table rather than a pivot table.

As for your calculations, I think we would want to alter both of the calculations to be LAC-W rather than LAC-A so they will function better together.

One question I do have is, for the Max Appointment field, why are you wrapping it in a sum function? Do you want it to sum across all values displayed or sum the max by warehouse and how do you want the Process Trailers partitioned? A little more clarification will help me provide assistance.

Now, based on the information above, these are the current recommendations I’d give to fix the calculated fields:

Processed Trailers = distinctCountOver(ifelse({anchor_date} = {checkin_shift_date}, {inbound_ship_appointment_id}, NULL), [{warehouse_id}], PRE_AGG)

I set this to partition by warehouse_id, but you can add other partitions or remove them depending on your expected output.

Max Appointments = sumOver(maxOver({max_appointment_constraint}, [{warehouse_id},{appointment_type},{anchor_date},{Shift Name}], PRE_AGG), [{warehouse_id}], PRE_AGG)

If you are going to be using these bracketed partition fields in your calculated fields and nesting multiple fields together, I always recommend using LAC-W aggregations. They function really well together. Let me know if this helps!

3

Hello @nebadi, since we have not heard back from you with any remaining questions on this topic, I will mark my previous response as the solution.

If you need any further assistance with this issue, please let me know and I can guide you further. Thank you!