Filter Column Data By Value

phesson
phesson Member
in Beast Mode

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!

Best Answer

  • jessdoe
    jessdoe Contributor
    Answer ✓

    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.

Answers

  • jessdoe
    jessdoe Contributor

    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
    pauljames Contributor

    @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.

    IF I SOLVED YOUR PROBLEM, PLEASE "ACCEPT" MY ANSWER AS A SOLUTION. THANK YOU!

  • ST_Superman
    ST_Superman Domo Employee
    edited November 2023

    @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
    phesson Member

    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.

    SS 2023-11-27 at 4.07.47 PM.png

  • jessdoe
    jessdoe Contributor

    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
    phesson Member

    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
    jessdoe Contributor

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

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

    Removed, still that pesky error.

  • jessdoe
    jessdoe Contributor

    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
    phesson Member

    Yes, same thing

  • jessdoe
    jessdoe Contributor

    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
    phesson Member

    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
    jessdoe Contributor
    Answer ✓

    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.

Categories