Domo beastmode and pivot question

ISD_Aman
ISD_Aman Member
in Charting

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


Tagged:

Answers

  • RobSomers
    RobSomers Coach

    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • ISD_Aman
    ISD_Aman Member

    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
    RobSomers Coach

    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?

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • ISD_Aman
    ISD_Aman Member

    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
    RobSomers Coach

    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • ISD_Aman
    ISD_Aman Member

    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
    RobSomers Coach

    Could you post your beast mode?

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • ISD_Aman
    ISD_Aman Member

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

    and

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

    Both coming back empty.

  • RobSomers
    RobSomers Coach

    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.

    **Was this post helpful? Click Agree or Like below**

    **Did this solve your problem? Accept it as a solution!**

  • ISD_Aman
    ISD_Aman Member

    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

Categories