Chart Not Correctly Showing Tally Results

[Robert_Ham]

Hi,

Can anyone please help with this?

I have a problem with this chart.

If you look at April in the Tally 2019 tab it comes up with 4 raised and 6 completed.

If you look at the Master Schedule tab then 4 items were raised and it shows 6 completed.

Can you help with this?

Best Wishes,
Rob

[kersplash]

When I open the file it shows 4 raised and 7 completed in both sheets.

[BadlySpelledBuoy]

Same for me. Perhaps your auto calculation is turned off?

Or perhaps the question here is really that there are more closed in Apr than were opened?
If so, that's because some of the records closed in Apr were opened in previous months.

BSB

[Andy Pope]

What exactly is the problem?

BTW I see 7 completes

[Robert_Ham]

Hi BSB,

Thank you for your support.

The tally shows for April, 4 raised 7 closed and -3 open.

The Master Schedule shows for April, 4 raised, 2 closed because they have dates in the completed column.

The tally should reflect the Master Schedule.

RH

[jme1013]

Not 100% sure what you're asking.

Are you wondering why you have a negative number on your chart?

The data that the chart is pulling from for April, you have "Tasks Raised" as 4 and "Tasks Closed" as 7.

Your "Tasks Open" cell is calculated from Raised - Closed, which for April equals -3

You can't have more "Raised" tasks" than "Closed" tasks, otherwise you'll always have a negative number for "Open".

Are you wanting your "Open" tasks to display what was opened during a particular month or a cumulative total for all months?

[Robert_Ham]

The chart should display what has been raised and what is closed during a particular month. In this case April.

This should not be cumulative.

Sorry I do not know how the formula works.

[BadlySpelledBuoy]

Put this in C4 on the tally sheet and copy down if the table doesn't do it for you:
=COUNTIFS(Table_Master[Date],">="&[@Start],Table_Master[Date],"<="&[@End],Table_Master[Completed],"<>")

You will also need to copy the formula from B5 and paste it into B4.

BSB

[jme1013]

Your formula is just subtracting Raised from Closed to get a total for Open, regardless of the month.

The Raised total is correct for April, but the Open displays as negative because you had tasks from March that were closed in April.

So, you had 4 Raised Tasks in April and 7 Closed Tasks in April. However, only 2 of those 7 were Raised and Closed in April. The other 5 were Raised in March.

You need to have your chart data look for at the dates that were Raised and Closed in the same month to populate the graph the way you are wanting.

[BadlySpelledBuoy]

See attached with changes made for you.

BSB

[Robert_Ham]

Hi BSB,

Works as expected now.

Thank you so much for your support.

Best Wishes,
Rob

[Robert_Ham]

Hi jme,

Thank you, I now understand where the problem was.

Best Wishes,
Rob

[BadlySpelledBuoy]

Happy to help Rob.

Have a good day.

BSB

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.