Discussions

Expand all | Collapse all

Cumulative bar graph/nested IF

  • 1.  Cumulative bar graph/nested IF

    Posted 07-19-2017 16:14
    Hi, I'm tryign to produce a cumulative bar graph showing cumulative months along the X axis & value on the Y axis, i can create the below which provides total value by month:

    If([Month]="01. Jan", [YTD Jan],
    [Month]="02. Feb", [YTD Feb], 
    [Month]="03. Mar", [YTD Mar],
    [Month]="04. Apr", [YTD Apr],
    [Month]="05. May", [YTD May],
    [Month]="06. Jun", [YTD Jun],
    [Month]="07. Jul", [YTD Jul],
    [Month]="08. Aug", [YTD Aug],
    [Month]="09. Sep", [YTD Sep],
    [Month]="10. Oct", [YTD Oct],
    [Month]="11. Nov", [YTD Nov],
    [Month]="12. Dec", [YTD Dec],
    0)

    And if i do only one i.e. (i just used Jan as an example):

    If([Month]="01. Jan", [YTD Feb]+[YTD Jan], 
    0)

    This adds up Jan & Feb

    However if i start with Jan on it's own and then try Feb:

    If([Month]="01. Jan", [YTD Jan], 
    [Month]="02. Feb", [YTD Jan]+[YTD Feb], 
    0)

    It displays Jan on it's own but nothing in the graph for Feb so 0.

    Any assistance or guidance would be much appreciated?

    Thanks


  • 2.  RE: Cumulative bar graph/nested IF

    Top
    Contributor
    Posted 07-20-2017 04:28
    So "IF" and "Case" statements only return one value.  The first true value in the sequence.

    If you want a cumulative function like this. I'd recommend that you set up a 'parent summary' table to accomplish this.

    If you set up a "Months" table, and "Years" table, you should be able to automatically roll up all the values for YTD and sequenced data.