Domo beastmode and pivot question

ISD_Aman

Hello,

I'm trying to automate a file to run calculation in DOMO on a card.

In the attached I added a sample data and a pivot table to show what I'm trying to get to.

I was able to get the pivot completed in DOMO, but the additional column Collection Total, I cannot figure out how to create a beast mode for this, I'm trying case statements but I am not going very far.

The pivot is summarized by Job org and Class, while the additional column needs to add the totals of classes for a job org and subtract by the 8000 class.

see attached.

Any help will be much appreciated.

DOJO help.xlsx

RobSomers

You can do a beast mode for Collection Total something like this:

case when `Class` = 8000 then `Sum of Net`*-1 else `Sum of Net` end

Then you can just do a sum of the Collection Total column when you add it to your table.

ISD_Aman

Hello RobSomers,

Thanks for taking time. In this case statement the summing of the rest of the classes together for that job org is not accounted for.

For Job org = a the correct formula would be (Sum of net of class 1000+Sum of net of sum of 1600+Sum of net of sum of 2000+Sum of net of sum of 2500 ) - sum of net of class 8000.

So its like grouping with job orgs then performing the said formula.

RobSomers

So you've done the pivot in a Magic ETL and the pivot is the output and what the formula needs to be based on?

ISD_Aman

The formula needs to be based on the sum of the pivot columns.

In the attached sample dataset:

• Columns A through e are dataset.
• Column H through N are pivot of the data, was able to do this using Pivot table chart.)
• Column P - Is the Collection total which is driven by formula ((Sum of net of class 1000+Sum of net of sum of 1600+Sum of net of sum of 2000+Sum of net of sum of 2500 ) - sum of net of class 8000.) ***I left the formula in excel for reference, select any box in the Collection Total values.

I hope I am explaining it the correctly.

RobSomers

It's going to be a little tricky, because doing it as a pivot table, your beast mode is basically going to be based off of the dataset and affecting that, and not the pivot table directly. If you're fine with the renaming the Grand Total Column to be your Collection Total column, and you're ok with the '8000' column showing as negative, then you could do the beast mode above and use the beast mode as your values instead of Sum of Net. I've attached your file that I modified with what this would look like on the 'New' tab, with Collection Total being the equivalent of the beast mode.

DOJO help 2.xlsx

But if you want it as a separate column, then I think what you'd have to do is do a pivot in MagicETL so you're dataset is in the pivot format to begin with, and then you could do a beast mode that adds each column but subtracts the 8000 column.

ISD_Aman

Hello Rob,

I went ahead and created an ETL pivot, JL org key as row and the classes as columns.

I then created a beast mode to sum and subtract the columns as needed.

The beast mode is properly validated from formula perspective, but is not returning any results, just empty fields.

RobSomers

Could you post your beast mode?

ISD_Aman

Tried it this way: (sum(`1500`) + sum(`1600`) + sum(`2000`) + sum(`2500`)) - (`8000`)

and

(`1500`+ `1600`+`2000`+`2500`)-(`8000`)

Both coming back empty.

RobSomers

Are your 0's in your data actually 0 or are they null (Ex: row 'a' column '2000' in your example). If they are null then you will need to do IFNULL('Column',0) for each of your columns in the beast mode, or use your ETL to change the nulls to 0. This is the only thing I can think of at the moment.

ISD_Aman

Hello Rob, I think the formulas are now working fine. Thank you for your assistance with how to get this to work. fixing the null's actually made the values appear.

I am now experiencing issue with ETL Pivot, where its just pulling one row value rather than summing all the values of row that matches the Org code for rows. I need to troubleshoot this one first to complete this exercise.

Aman