CASE When order

IgneP

Hi team,

I need to figure out how to order my Hub Music Factory WHEN cases.

The OUTPUT that I want to get is that when I select ONLY nmOrganizer - Hub Music Factory, the result comes out as the total sum of all Hub Music Factory values in green *where there is also specified nmPerformance plus /12500.

BUT when I select nmOrganizer - Hub Music Factory and nmPerformance example 'The Offsprings' then I see -2294.62.

Now the issue is that if I leave total sum on top of my CASE logic *As pic above, I always see total value no matter if I specify the Performer.

total sum>

WHEN nmOrganizer= 'Hub Music Factory S.r.l.' THEN -(12500+2294.62+964.54+1192.98+472.39+10000)

If I add total sum at the end it splits my my table in 5 columns as here >

any idea how to fix this?

Godiepi

Try your case statement this way

CASE 
  WHEN `mOrganizer` = 'Hub Music Factory S.r.l.' 
	THEN
          CASE
	     WHEN `nmPerformance` = 'The Offspring' 
		THEN -2294.62
	     WHEN `nmPerformance` = 'Social Distortion' 
	        THEN -964.54
	     WHEN `nmPerformance` = 'RECONSTRUCTION TOUR 2025' 
	       THEN -1192.98
	     WHEN `nmPerformance` LIKE'%BAY%' AND `dStart` LIKE '%2024%' 
	       THEN -472.39
            WHEN `nmPerformance` LIKE'%BAY%' AND `dStart` LIKE '%2025%' 
	       THEN -10000
	    ELSE -(12500+2294.62+964.54+1192.98+472.39+10000)
           END
  WHEN `mOrganizer` = 'J Basket Monferrato SSDARL' 
      THEN - 2916.67
  WHEN `mOrganizer` = 'Fondazione Reverse ETS' 
      THEN - 7000
  WHEN `mOrganizer` = 'LATIN FESTIVAL S.R.L.' 
      THEN - 55000
  WHEN `mOrganizer` = 'AMA Entertainment Srls' 
      THEN -6416.67 
  WHEN `mOrganizer` = 'LIBERTAS LIVORNO 1947 S.S.D A R.L.' 
      THEN -1200
ELSE 0
END

IgneP

Hi and thank, unfortunately it did not solve the issue.. it still splits in 6 columns

DavidChurchman

• 1)

• I don't think that it's this BeastMode that's causing the data to split across columns, since you're summing it. It's probably the TEST column, which I assume is the same one as this discussion:
  https://community-forums.domo.com/main/discussion/69146/can-i-sum-2columns-one-being-aggregation-sum-and-other-no-aggregation-fixed-number

To quote myself from that discussion:

"One note of caution is that your current set-up won't work if you have multiple nmPerformers in your data. You either need to insure a single performer is filtered or include the performer as a column in your table."

2)

I've been following your work on this table for a few weeks, and I admire your tenacity, but I want to recommend a different approach. In general, I would recommend using BeastModes to do math on data that you already have in your table. You seem to be using BeastModes to add new data, which is not a sustainable way to store/update/view your data. Create small tables of these expenses and join them to your data before going to the analyzer and BeastModes. Then, if new expenses need to be added, you can add them to your table rather than having to manually add it to the BeastMode every time.

IgneP

thank you David, it's just that I am pretty new in DOMO and still learning, I guess I just learned a beast mode and kept applying it everywhere :))).

I am eager to learn new skills, I have never created a smaller tables and joined them to my data. Would you recommend any education online, links - so I can have an example on how it works. thank you in advance!

DavidChurchman

There are the Domo University courses, which will have a lot of examples of how to apply everything in Domo. The only caveat is I feel like most of the Domo courses assume your data is already structured relatively cleanly, maybe coming in from system.

For a theoretical grounding on how to structure your data from the ground up, I recommend this article, which is how to structure your data in a "tidy" format, and is a relatively quick read:

https://vita.had.co.nz/papers/tidy-data.pdf

If you want to get really into the weeds, look for resources on "database normalization", which is the concept of reducing your data into small tables that can be joined together. I don't think your data needs to be truly "normalized" in Domo, but the concepts/thinking behind normalization are useful.