I have the need to do the following but I am struggling a bit.
I have a dashboard that has Month Names running across the top - Jan Feb Mar Apr May Jun Jul, etc.... and a bunch of charts that show trends.
I then have some financial data that has monthly spend and a forecast for budget.
I need to have a gauge chart that reflects the "% of budget spent". The issue that I am having is that users select multiple months since the rest of the charts are based on trending data month over month and this will not be that way.
Essentially I need to do the following.
If the most recent month selected is Jan, Feb, Mar, or Apr then I need to select the F0 value from "ForecastType"
If the most recent month selected is May, Jun, Jul or Aug then I need to select the F4 value from "ForecastType"
If the most recent month selected is Sep, Oct, Nov, or Dec then I need to select the F8 value from "ForecastType"
Once the forecast type is selected I will then take that value and divide it by 12, then multiply that by the number of months selected since the forecast number is a yearly figure and I will need that on a monthly basis. Once I have that I divide that by the sum of the monthly spend to get my ratio.
