Level Aggregate Functions and Order of calculations

Question & Answer
, , ,
1

I am building an analysis that shows hourly movements of flights in an airport. My raw data contains every arrival and departure and when aggregated looks like the following

image

I want to plot the Average & Max # of flights by hour for any given date range and journey direction (Arrival, Depature). Since the raw data has the flight time I use that to get the hour.

I need the Total # of flights (row counts) by Flight Date, Journey Direction and Hour per the screenshot above. In the visual I want to show the Max # of Flights by Hour with the analysis filtering based on Flight Date and Journey direction.

If we use the screenshot above following are some possible results

image

I create the following calculated field as I first want a Sum to get the Flight Count by the dimensions I want to group by and then a Max to get the Max

image
image741Ă—147 4.79 KB

Following is the result when I have Flight Date, Journey Direction and Hour in the table visual and flight date filtered for Mar 1 and 2. This result is OK

image
image965Ă—323 33 KB

When I apply a filter on Journey direction as well things are OK
image

Now, when I remove Journey direction from the visual the data still looks fine
image

However, when I remove the Flight date as well from the visual the Max goes wrong. I would have expected 33 for Max Flights. But the initial grouping that includes the Journey direction causes the Max to take 21
image
l

Any suggestions to get this formula right?

1 Like
2

Hello @Giridhar.Prabhu, what if you utilize the option to hide the Flight date field from the visual rather than removing it entirely? That will still allow it to utilize it in the calculated field and maintain the values you are seeing, while making it invisible for the user who is viewing it. That is likely the best way to accomplish this.

3

Hi @DylanM,

Hiding does not aggregate the data.

image
image708Ă—528 39.6 KB

Is there any way I can achieve this in the calculations?

4

Hello @Giridhar.Prabhu, when you select hide on Flight Date, it alters how the data is being aggregated?

5

Hello @Giridhar.Prabhu, I think I understand, you don’t want to utilize max outside of your Max Flight calculation field, you will just want to remove Journey Direction from the partition. It would look something like this:
sumOver({Total Flight}, [{Flight Date}, {Flight Hour}], PRE_AGG)

That will give you the total number of flights, Departures or Arrivals, within that hour.

6

Hi @DylanM,

I did try those options; but filtering the journey direction (say I filter to show only Departure) it would not consider that in the aggregation and I would still see the Max as 33 while I would expect it to change to 21

7

Hello @Giridhar.Prabhu, if that is the case, then you might just want to use an ifelse statement to determine which LAC-W calculation you want to utilize. If you are utilizing a parameter controlled filter to determine journey direction, we can use that parameter value to determine which option to return. One thing to note, if Select All is chosen, the parameter will return null. I’ll write an example of the function below:

ifelse(
isNULL(${Journey}), sumOver({Total Flight}, [{Flight Date}, {Flight Hour}], PRE_AGG),
sumOver({Total Flight}, [{Flight Date}, {Flight Hour}, {Journey Direction}], PRE_AGG)
)

Now, if you have all Journey Directions selection, it will sum the 2 total flights, otherwise, it will return the total flights for the journey you are filtering by. This should resolve the issue you are facing.

8

Hi @DylanM

Thanks for the suggestion. I tried the logic and ran into a technical error below

image
image782Ă—404 13.8 KB

So, I used the following thread to make slight changes as suggested by @Max to make it work.

Here’s my version of the formula.

image
image909Ă—243 6.31 KB

1 Like
9

Hello @Giridhar.Prabhu, I didn’t realize the Journey parameter was a multi-value parameter, so yes, the in aggregation works great for that. Thanks for letting me know that you were able to resolve it!

1 Like