Creating custom columns for use in tables

EagleForceOne
EagleForceOne Member
in Charting

End Goal: Our goal is to create a table that can successfully group elements by row (such as state) and implement column values that are based on custom formulas. The input data for this table consists of our company's sales data.

 

What I am attempting to do currently is create a pivot table that groups our monthly sales by Sales Region (rows), and then the various columns are various units of measure (MTD cost, prior year MTD cost, etc.)

 

 

Challenge: It has been fairly easy for me to create a column for MTD cost, as that is simply "sum of revenue" with a date filter for the current month. However, we want to begin to implement more complex logic into columns. One example of such logic is a column called "MTD trending."

 

Requirements: The data MUST be groupable by rows. We MUST be able to create custom measures and add them as volumn values within the table.

 

 

MTD Trending = ((MTD Revenue / number of business days elapsed in current month) * number of total business days in current month) / MTD Quota)

 

The end result is a % value of the MTD quota that we are expected to finish near. How might I accomplish the creation of this column (and other columsn with complex formulas) within a sumo table?

 

Tagged:

Best Answer

  • dthierjung
    dthierjung Contributor
    Answer ✓

    You should be able to accomplish all of this using Beast Mode formulas. You can put the formula you provided above into a beast mode, replacing the appropriate logic and dataset columns as appropriate. You'll probably need to use a few of the available datetime functions to calculate the number of days passed, and total days in the current month.

     

    The trick, however, is that you'll need to create them in a different card using the same dataset as the Sumo card. While creating the beast mode, remember to check the box near the bottom that enables sharing across the dataset.

     

    Once you've made your beast mode and saved it and the card, you can access it as new column on your sumo card.

     

    Let me know if you need further help on anything I've put forth.

Answers

  • dthierjung
    dthierjung Contributor
    Answer ✓

    You should be able to accomplish all of this using Beast Mode formulas. You can put the formula you provided above into a beast mode, replacing the appropriate logic and dataset columns as appropriate. You'll probably need to use a few of the available datetime functions to calculate the number of days passed, and total days in the current month.

     

    The trick, however, is that you'll need to create them in a different card using the same dataset as the Sumo card. While creating the beast mode, remember to check the box near the bottom that enables sharing across the dataset.

     

    Once you've made your beast mode and saved it and the card, you can access it as new column on your sumo card.

     

    Let me know if you need further help on anything I've put forth.

  • EagleForceOne
    EagleForceOne Member

    Okay - so my follow up question is "what datetime function do I use?" but that is an entirely separate quesiton that I'll need to ask.

  • rado98
    rado98 Contributor

    As for the time funtion

    If your data set doesnt already have every bussiness day of the month (future ones included) on at least 1 row

    I would add a column with total number of bussiness days for the month. This would need to be done by joining a to the data set I would join to the MTD Quota rows.

     

    So, crudely expressed

    SUM(Revenue) / Sum(Distinct( Bussiness Days*)) * AVG(Total Business Days) / SUM(MTD Quota)

     

    Your Date Range should be "This Month)

     

     

    * You will need a case statement to discount Sat/Sun

This discussion has been closed.

Categories