Dynamic Index

user10540

Hello,

 

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 ()

 

 

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).