Filter Column Data By Value

phesson

To preface, I am a beginner at using DOMO so this may be something easy that I overlooked. I've blended two datasets and am attempting to have a column show aggregate data for specific locations based on a particular value.

Example dataset one shows KPIs for a bunch of different metrics based on location, all that is working fine and sums up correctly. The second dataset has additional data I want displayed / summed in additional columns by location. The issue is the second dataset also contains data from sources I do not want included in the sum for each location (not sold there but still "coded" under the locations). When I add the "source" column into the filter on the visualizer, it filters down all the other data to only include when that second source also has data for the location / date (which displays about 10% of the other data too). I'm trying to do this in beastmode as I do not want to create a new card (tons of beastmode formulas already in there I need).

Location	Visits	Revenue	Profit
ABC	50	$1,200	$500
DEF	47	$1,000	$600
GHI	63	$893	$800

Made up number example above, but this is how the data aggregates (based on the dates selected), and I'm wanting to have the NEW column (pulled from the other dataset, which all works correctly) display for each row, but I can't just sum or count the data within that column as there are different "sources" the new data comes from and it needs filtered down to only be "on site".

Any help is appreciated!

jessdoe

Ok good. I think at this point there'd have to be something off with alignment of the rows in your blend if the aggregation at the beastmode level isn't working in this scenario.

jessdoe

Are you wanting your filter column to apply to only one of your aggregated columns? If so, try adding a beastmode for the aggregated column for which you want the filter to apply and add the beastmode column to your report rather than the aggregated column you created in your blend:

sum(case when filter_column= 'source' then value_column_from_blend) end

pauljames

@phesson , hey there! have you tried aggregating on multiple columns in the magicetl? You could create a new column for the other dataset and name source then ensure the other dataset has the column as same source name. Then aggregate on all of the necessary dimensions and the new source column. Do you have a pic of magicetl? Might be more helpful that way.

ST_Superman

@phesson

I believe that @jessdoe is on the right track here. The only thing that I would add to her code is an "ELSE 0" before ending the case statement.

SUM(CASE WHEN Source = Location THEN ValueField ELSE 0 END)

phesson

Thank you for all the replies!! I was on the right track with this, but I'm still getting this error when trying to save the Beastmode calc using the examples above and updating to my data.

This is the error I get, can't find any info on why it is throwing.

jessdoe

You may need to wrap your value field in an ifnull:
ifnull(`value_field`,0)

If that doesn't work please paste the formula.

phesson

SUM(CASE WHEN Source = 'POS' THEN sum(Qty) ELSE 0 END)

CASE WHEN Source = 'POS' THEN sum(Qty) ELSE 0 END

Tried these two. There are two columns I'm needing to sum and was going to do a calc for each one. Qty & Total (one is quantity of product sold, other is price of product sold).

Thanks

jessdoe

You don't need sum(Qty). Just Qty

phesson

https://community-forums.domo.com/main/discussion/comment/83163#Comment_83163

Removed, still that pesky error.

jessdoe

Did you already try to the null wrapper to account for empty rows in your data?

sum(case when source='POS' then ifnull(`Qty`,0) else 0 end)

phesson

Yes, same thing

jessdoe

Strange. Is Qty a "measure"? If it's rolling up as a string rather than a number in your blend, that's the only other reason I can think of you'd have trouble adding it tot his formula.

phesson

Yes, QTY is showing up as a measure. Also, I don't necessarily need to sum these I guess, I just didn't know how to have this column display the QTY based on what source is selected. I believe in the other dataset this is already summed up and displayed by transaction.

jessdoe

Ok good. I think at this point there'd have to be something off with alignment of the rows in your blend if the aggregation at the beastmode level isn't working in this scenario.