Looking for Static Aggregated Value

Q&A
, ,
1

Hi community!

I was trying to create a calculations for one of my tables for multiple days, and one of them is a LAC-A that helps me aggregate my shipments.
image

But when I try to put in in a table adding a new field, my aggregation is changed because I’m selecting multiple days for this visual (Ex. 2025-07-20 to 2025-07-24) and some registers for the added field are not present in my data of some of the selected days.

This is what’s happening

image
image328×219 1.9 KB

What I want is all of my shipments maintaining the same value across all fields.

image
image325×220 1.88 KB

Is it possible to achieve this?

2

Hi @cesdom,

If you want the max value to be the same for all the rows in your partition, you need to use LAC-W instead of LAC-A.

Try this:

maxOver({dispatched_shipments_ds_pt}, [{dim_location}, {period_date}, {dim_provider_type}], PRE_AGG)
3

Hi @David_Wong thank you so much for taking the time to answer the question!

If I try the LAC-W formula, my values change once I add the calculate field into the visual.

image
image507×214 12.4 KB

The test field is the one with the maxOver suggestion and the other one is my original LAC-A

4

Can you show your field wells so that I can see what aggregation type you have selected?

Can you also edit the column headers in your screenshot to match the fields in your dataset for clarity? Is group the same as dim_provider_type? Is sub-group the same as dim_location?

image
image321×246 1.77 KB

5

Sure! I changed the corresponding columns over the description in the first example, in this case, “group” is provider_type and “sub-group” is “dim_shipment_reason” I’m looking to not show the “dim_location” on the table but it is used as a filter in the visualization.

For the aggregation type I have them both as a “sum” aggregation

image
image248×323 9.46 KB

6

Can you change the aggregation for your TEST calculated field to min instead of sum?

7

With MIN my data looks like this

image
image539×229 11.6 KB

8

If I’m understanding correctly, you want all 4 values for DSP to be the same and all 4 values for IHS to be the same. Is that right?

Can you adjust the partition in your calculated field as follows?

maxOver({dispatched_shipments_ds_pt}, [{provider_type}], PRE_AGG)
9

Hi @David_Wong! I tried this and the result it’s the same as before. I’ll try to fix it in my SQL query so I can manipulate my data in my dashboard, thanks for the help anyway!

10

Hi @cesdom,

Hope this message finds you well! Just wanted to check back in regarding your post since this topic has been open for a while. Were you able to find a workaround by updating the SQL query or are you still encountering issues? If we do not receive a response within the next 3 business days, I’ll close out this topic.

11

Hi @cesdom,

Since we have not received a response, I’ll go ahead and close 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!