BeastMode code to take one value from each category

miguel_bustamante

This question is going to be really difficult to describe, but I will try anyway.

I am trying to get a unit count for each facility, the problem is that bc we filter by time and different facilities can gain units over time, I can not just have one row per facility, I have a row for each month. That being said I need it to pull the unit count just from the final row available per facility.

Image 1: This is the graph I am trying to get to work

Image 2: How the data categories are set up the name column represents each facility. I will refer to the name column as the facility column going forward

Image 3: Data categories cont. (Ignore occupancy columns).

I need to pull just one units value per facility category, I tried setting it to average, but it takes the average for the whole state which is wrong. There are typically 3 or 4 facilities per state. I need the state name to be states or the card doesnt work.

Image 4: Here you can see the discrepancy, the unit count by state should always equal the units gauge. But I can not figure out how to get the state value to only pull one value per facility, while being able to keep the state name to the states.

MarkSnodgrass

If you are statically setting the filter and will have to change it each month you should be able to do it dynamically by creating a beast mode that looks something like this:

CASE WHEN datefield = MAX(datefield) THEN 'Include' ELSE 'Exclude' END

Drag this beast mode into your filters and filter to Include.

DavidChurchman

Is the same dataset powering your "Units" gauge as the map? If the goal is to get the same number to appear on both that and the map card, a good place to start would be what is different about those two cards

miguel_bustamante

@DavidChurchman So the difference between the two is the Units data set only has one entry per facility, it does not have to worry about the time filter like the states card does. I know why they are different, but I can not normalize that as I need to keep the months filter working on the states card, but not the units card. While setting the units gauge to the states data set would display the same number. Instead of one being right and one being wrong, they would both be wrong.

My question isnt as much about setting them equal to eachother, but its about making the states card pull only one value per facility. Preferably the most recent value available based on the time filter set. But having it be stuck on the Q2-24 also works as a temporary bandaid, until i figure out how to make it work with the time filter.

miguel_bustamante

I figured it out, I set a filter to the last month available within the actual card. Not great but better than nothing. Open to other suggestions, as of right now it wont let me look at unit counts in other months without breaking it.

MarkSnodgrass

If you are statically setting the filter and will have to change it each month you should be able to do it dynamically by creating a beast mode that looks something like this:

CASE WHEN datefield = MAX(datefield) THEN 'Include' ELSE 'Exclude' END

Drag this beast mode into your filters and filter to Include.

miguel_bustamante

@MarkSnodgrass Thank you so much that is a much better solution