Build PivotChart with sum by start and end date

Hi,

I have a table with StartDate, EndDate, Value and some additional characteristics.

-I want to create a Chart
-X starting with the lowest of all Dates and Ending with the highest Date.
-Y displays for each X the Sum of Values where X >= StartDate & X < EndDate.
-It should be possible to filter the data by using the other characeristics and the Chart should automatically adapt.

I tried a lot but the only thing I could do, was to build a static list of values using Visual Basic and display it
as chart. But this makes filtering impossible.

Any hint to sovle this problem is highly appreciated.

Thanks,
Andre

Please include a sample spreadsheet so we can better see your problem.

Hi,

find attached an example. It's about software quality statistics:

I added a Pivot to show the Sum and Count of the Score column, but
I don't know how to consider submit and fix dates for the sum.

Andre

Sometimes you have to give the pivot table a helping hand. In this case, with a helper column. If you have a difficult time breaking things out or limiting things, a helper column in the source data can get you out of trouble. I have this issue with some of my pivot tables. I often track metrics concerning wireless devices, but I don't want to include rows where the device name is blank. One issue with pivot table filters is that if I select (All) and a new device is discovered in the data, it will show up along with the blank. But if I deselect blank (or any other model) and a new device shows up in the data, it will come in automatically filtered out. So I use a helper column that checks to see if the device name is blank and evaluates to True when it is not. I use that for my filter. I depend on the formula in my helper column to do my filtering rather than try to make it work in the pivot table.

You have a similar situation with your data. So I added two fields on the data page to identify the desired start and end date (Cells L1 and L2). These cells could even be calculated if you wish to show the last 6 months or whatever. Then I added a column called Within Date with the formula =AND(E2>=$L$1,E2<$L$2). This evaluates to TRUE if the date in the spreadsheet is within the two dates. Then I rebuilt the pivot table using it as a filter.

I did one more thing. I converted the data into an Excel Table. One advantage that Excel Tables have is that they grow and shrink with the data. So if you add a column to the data source, and then refresh the pivot table, the column will be available for use in the pivot table automatically. Also you do not have to look at the entire million plus rows of data. All you need to look at is the exact amount of rows you have. Excel tables always know how many rows they have.

This wiki has more information on Excel Tables: http://www.utteraccess.com/wiki/inde...ables_in_Excel.

Hi,

thanks for your answer, but that's not exactly what I wanted.

If I have an entry like:

SubmitDate FixDate
23/04/2015 28-10-2015

I want to sum it in the chart to ALL dates between Submit and Fix and not only to the FixDate.
The target is to get a chart with showes the open Bugs for each date so that I can see the trend.

If I have 3 entries like:

SubmitDate FixDate Score
01/01/2015 03/01/2015 1
01/01/2015 02/01/2015 1
01/01/2015 --/--/---- 1

I want to show:
01/01/2015 3
02/01/2015 2
03/01/2015 1
04/01/2015 1

The sum of all bugs which are submitted, but not yet fixed.

Does this make it more clear ?

Thanks,
Andre

OK, so add another condition in the helper column =AND(E2>=$L$1,E2<$L$2,ISBLANK(F2)).

This will get all the submit dates within the range that have no fix date.

Hi,

Not really. I tried to put something together with your Check.
It's not really nice, but just to make it more clear.

I made a helper table where I generate a matrix of dates and the check if the date is inside submit and fix date.
Afterwards I put the pivot on the helper and sum the columns... I know it's not nice but I hope it helps to understand
what I want to achieve.

But anyway, I want to thank you for your help so far.

Andre

I think I see what you want now by reverse engineering the Helper page. You want a contiguous range of dates. For each one of these dates, you check to see if it is greater than or equal to the submit date. You also check to see if it is less than the fix date. If it is, then you take the score. If it is not, your formula defaults to false (which I assume is a zero).

How do you want to treat the situation where you have a submit date and no fix date? Do you want to ignore these records or since the item is still open do you want to count it as long as the submit date is within the range.

I think this can be done with SUMPRODUCT. I just need to know if my assumption is correct and how you want to handle the lack of a fix date.

The ones which are open should count for all dates since the SubmitDate.

Attached are two spreadsheets. One of them uses the ranges as you gave us. The other converted the range into an Excel table. I included both to illustrate two of the advantages of tables:

1. They use natural language syntax, so it is easier to build and understand the formulas: intellisense does most of the work for you.

2. They are extandable, that is they grow and shrink to accommodate exactly the amount of data you have. There is no guessing as to how many rows to include.

The formulas for both books are:
=SUMPRODUCT((Data!$G$2:$G$161)*(Data!$E$2:$E$161 <= Sheet1!A2) *(Data!$F$2:$F$161 >Sheet1!A2))+SUMPRODUCT((Data!$G$2:$G$161)*(Data!$E$2:$E$161 <= Sheet1!A2)*ISBLANK(Data!$F$2:$F$161))

=SUMPRODUCT((Table_Data[Score])*(Table_Data[SubmitDate]<= Sheet1!A2) *(Table_Data[FixDate] >Sheet1!A2))+SUMPRODUCT((Table_Data[Score])*(Table_Data[SubmitDate] <= Sheet1!A2)*ISBLANK(Table_Data[FixDate]))

I think you'll agree that the second formula is easier to read and if you add data beyond row 161 you don't have to change it.

The formula uses two SUMPRODUCTS. The first sumproduct totals the scores where the date to plot is between the submit date and the fix date. The second sumproduct totals the scores where the submit date is equal to or earlier than the plot date, but the fixed date is blank.

To learn more about tables see this article: http://www.utteraccess.com/wiki/inde...ables_in_Excel
To learn more about SUMPRODUCT see this article: http://www.utteraccess.com/wiki/inde...Array_Formulas

Hi,

I agree, the second formula is easier to understand.
Now I get one "static" chart, which is a already really good.

Is it possible to adapt this further to add the possibility to filter ?
I would like to be able to only see the Chart for
-One Project
-A Subset of specific Functions of one Project
-Only for High criticality
-...

Similar to Pivot-Charts, where I can add many kind of filters
and the chart adapts automatically to my changes.

Since the chart wors on the helper table, I'm not really able to filter on the original data.
The only possibility I can imagine is to add additional conditions to the formula, which is not soo comfortable.

Is it perhaps possible to enable Filters in the data tab and to take only the fields which are currently not filtered to update
the Helper table ?

Andre

I had to abandon SUMPRODUCT since it can't do wildcards which was important to the solution. I am using SUMIFS instead.

First I set up some data validation for Project, Function and Criticality. In cells L1:N3.

I set up a Lookups page, that can be hidden, to establish the dropdown lists. These are pivot tables that get a list of unique values from the data. I also elected to set up a cascading dropdown list for function. You have different functions for each project and the function list should change to fit the Project selected. I used the techniques in this article to set them up: http://www.utteraccess.com/wiki/inde..._%28Non-VBA%29

The article does not explain the if statement in the offset command that defines the named dynamic range, Function_List. I didn't know you could use an if statement in Offset. The if statement states that if you select (All) for project you have to look in a different place than if you selected a specific project. This article explains OFFSET and dynamic names: http://www.utteraccess.com/wiki/inde...Dynamic_Ranges.

The lists are dynamic. They will adjust to the data provided.

I have three static named ranges: Selected_Project, Selected_Function and Selected_Criticality. These are cells M1:M3. Basically they translate the dropdown values in L1:L3 into "*" when (All) is selected. They also make the formula easier to read.

This formula parallels the SUMPRODUCT solution. The logic is basically the same, although the syntax for SUMIFS is different. I added the three additional conditions.

=SUMIFS(Table_Data[Score],Table_Data[SubmitDate], "<=" & [@Date],Table_Data[FixDate], ">" &[@Date],Table_Data[Project],Selected_Project,Table_Data[Function],Selected_Function,Table_Data[Criticality],Selected_Criticality)+SUMIFS(Table_Data[Score],Table_Data[SubmitDate],"<="&[@Date],Table_Data[FixDate],"",Table_Data[Project],Selected_Project,Table_Data[Function],Selected_Function,Table_Data[Criticality],Selected_Criticality)

Hi,

sorry for the late answer. This solution works like a charm. Thanks a lot for your help on this topic.
That's really Excel-Magic.

Andre