Dynamic Index




I am trying to standardize multiple metrics over time by creating an index, on a max 100pt scale. Ideally, the index value would change when user changes the date range.  The attached spreadsheet shows what I'm hoping to accomplish. I'm trying to do this through beast mode calculation rather than ETL.


Thanks in advance for the help!


  • GrantSmith

    Hi @user10540 

    This is simple enough with a window function in a beast mode. From looking at your spreadsheet you're essentially looking to see what % of the maximum number within your date range is. This is a great case for window functions. This is a feature switch so if you don't currently have it you'll need to talk to your CSM to get it enabled.


    `X` / MAX(MAX(`X`)) OVER ()

    This will get you a decimal representation of a percentage. You can then just format your column as a percentage with 0 decimals to line up to what you're showing in the excel document for format. I tend to leave percentages in decimal form to allow the card creator / designer to determine how many decimal places they'd like to show rather than assuming they want 0. Give them more flexibility.


    You can do the same for the Y index:


    `Y` / MAX(MAX(`Y`)) OVER ()



    **Was this post helpful? Click Agree or Like below**
    **Did this solve your problem? Accept it as a solution!**
  • jaeW_at_Onyx

    At Grant,


    I'm not an expert on the indexing thing but I believe MAX(SUM(X)) over () would be accurate for all use cases, whereas


    Max(Max(x)) would be wrong if you're not showing the data in the chart at the row level.  Example if there were two or three transactions per date, Max(Max(() would show the maximum of the max value per day, not the max of the total value per day. (which would mess up the index).

    Jae Wilson
    Check out my 🎥 Domo Training YouTube Channel 👨‍💻

    **Say "Thanks" by clicking the ❤️ in the post that helped you.
    **Please mark the post that solves your problem by clicking on "Accept as Solution"
This discussion has been closed.