Adding monthly targets

[Noel88]

So it is the start of a new year for us and the setting of new annual targets with monthly phasings. As the targets change each year they are not part of our data-sets and so I need a way in VA to add these into reports.

Last year as I was new to VA I simply put them in a text box or in one case added them to a speedometer. The speedometer works but doesn't show the month names. (see pic attached)Monthly targets 5226 EOY 5024 actual.

In excel I would have just used arrays and look-ups to populate the graphs.

Is there a way, when you can't modify the data tables, to add monthly targets for use in graphs and tables? Also in a way that shows future month targets not just months for which there is current "actual month" data.

A typical target array would look like this but there would normally be 110 teams. The total of all the months would be the EOY target.

	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun
Team 1	300	300	265	393	525	357	476	764	736	404	351	336
Team 2	277	308	312	370	468	298	469	811	755	533	200	342
Team 3	80	80	77	87	85	46	78	104	111	73	55	67

Using release 7.4

[Noel88 · Accepted solution]

In the end worked out how to do this using "if else" statements for each team. This works for both the cumulative target below and the individual months and allows me to produce graphs plotting target against actual. It isn't perfect but it works.

IF ( 'Month'n = 'JUL2020'd )

RETURN 396

ELSE (

  IF ( 'Month'n = 'AUG2020'd )

  RETURN 807

  ELSE (

    IF ( 'Month'n = 'SEP2020'd )

    RETURN 1190

    ELSE (

      IF ( 'Month'n = 'OCT2020'd )

      RETURN 1776

      ELSE (

        IF ( 'Month'n = 'NOV2020'd )

        RETURN 2532

        ELSE (

          IF ( 'Month'n = 'DEC2020'd )

          RETURN 2979

          ELSE (

            IF ( 'Month'n = 'JAN2021'd )

            RETURN 3661

            ELSE (

              IF ( 'Month'n = 'FEB2021'd )

              RETURN 4898

              ELSE (

                IF ( 'Month'n = 'MAR2021'd )

                RETURN 5974

                ELSE (

                  IF ( 'Month'n = 'APR2021'd )

                  RETURN 6559

                  ELSE (

                    IF ( 'Month'n = 'MAY2021'd )

                    RETURN 7084

                    ELSE (

                      IF ( 'Month'n = 'JUN2021'd )

                      RETURN 7576

                      ELSE . ) ) ) ) ) ) ) ) ) ) )