Target value based on current month and sub region


I have created a gauge that shows a defined target value based on the sub-region using the formula below, and it is working well. 

IF ($subregion="Oceania",186,IF($subregion="PR China",84,IF($subregion="Japan",133,IF($subregion="South Asia",100,IF($subregion="East Asia",143,646)))))

However, the target value actually gets less each month.   What I have put above is actually the target value for the end of the year (Dec) rather than the current month.  Is there a way to add this into the above filter?






  • Avatar
    Parker Selman Official comment

    Hi Richard, 

    Thanks for reaching out! 

    You can make an adjustment to this formula to return the target value for the current month with a LOOKUP that returns the current month's target value. This LOOKUP would look something like this (depending on your data): 

    LOOKUP(DATEVALUE(TODAY(),"MMM"),*Your Month Data*,*Your Target Data*) 

    This LOOKUP formula will take the current month, map it to the month column in your data source, and returns the target data found when a match to the current month is made. Please note that the exact formatting of the DATEVALUE function will depend on the format of your data. 

    With that in mind, this LOOKUP function will need to be placed where each target value is listed in your original formula, and you may need to point your LOOKUP function to different columns / data sourced depending on which subregion you'd like the target value for. 

    Hope this helps! If you have any further questions or require support for this, please log a ticket with our team and we'd be happy to help further! 


  • 0
    Richard Powell

    Hi Parker.


    Thanks for that.    As I am going to have a different Target value for each Financial period (the Financial period is slightly different to each calendar month), how would I list each of those per each sub region?


    I have created a date lookup table that I can refer to, and got working on a different chart using Lookup(@Date Completed,@Date,@Period)  @Date Completed was in my main source Data and the other two were from my date lookup (structure of that table is shown below)

    Date Week WeekNo Fiscal Week Period Full Date & Day Fiscal Quarter
    1/1/2021 Week 0 1 Week 01 FP-01 (Jan) Friday, 1 January 2021 Q1
    1/2/2021 Week 0 1 Week 01 FP-01 (Jan) Saturday, 2 January 2021 Q1
    1/3/2021 Week 0 1 Week 01 FP-01 (Jan) Sunday, 3 January 2021 Q1

    I am assuming my initial lookup would be as follows.

    LOOKUP(DATEVALUE(TODAY(),@Date), @Period, *Your Target Data*) 

    Do I need to create a new datasource that lists 12 rows for each sub region, listing the FP and expected value?

    (I hope all this makes sense).




  • 0
    Parker Selman


    With this setup, you'll need to have some data that links your period value to your target value. That could be a new data source if that doesn't already exist somewhere in your data. By that logic, your suggestion of a data source with the target value for each sub region, period, and expected value would meet your needs. 

    As an additional note, this extra step of converting the date to a period may need to be accommodated in your LOOKUP, specifically around the format of the date that the LOOKUP is expecting. 

    I believe this formula would accomplish this:

    LOOKUP(DATEVALUE(TODAY(),"MMM"),DATE_CONVERT(RIGHT(@Period,5),"'('MMM')'","MMM"),@Target Value) 

    This would convert your Period column into a date column that could be looked up against. 

    Hope this helps! If you require further support, I highly encourage logging a support ticket here.
    Logging a ticket allows us to work directly in your account, which could lead us to clearer answers on some of these questions. All the best! 


Please sign in to leave a comment.