Confused on how to approach problem

HCL Domino Domino Forum
1

I have been fighting with this problem for over a week now and I can’t get it solved. I was wondering if anyone could point me in the right direction or offer some tips on solving this…

Basically I have a view that I’ve set up with categories and one level of subcategories. The main category is “years”, such as 2004, 2005, 2003. The one subcategory is “months”; for each year category there is up to 12 months such as 01, 02, 03, 04.

Under the months category is documents that are created within that month. So for example in the “01” category of “2004” are documents created in January of 2004, and so on.

On the far right of the view I have a total column which adds up the “dollar amount” value for all documents within months and years. So for example, 2004 category will have “$105,001” and the months categories will have values adding up to that.

The dilemma is… I am trying to copy this category dollar totals data over to MS Excel so that I may organize and chart it. However, I am having serious issues trying to get the “total” value to be associated with the appropriate “month” and “year” values. I can get one, or the other, but not both as a pair.

For example, I would like to put the total for the month of February in the year 2001 in the appropriate row and column in the Excel spreadsheet… which is arranged sort of like:

2001 2002 2003 2004

Jan

Feb

Mar

Apr

…

Any tips on how to approach this?

2

Subject: Confused on how to approach problem…

I would create an LotusScript agent which exports the manually/automatically selected documents to a Excel-HTML file and opens that file in Excel. Needs maybe 30-60 min of coding, but it’s fun.

3

Subject: RE: Confused on how to approach problem…

I tried something similar in that I wrote code that exported appropriate columns from the view to an Excel spreadsheet. However, I still could not logically “associate” values from one column to another because they did not line up very well.

???

4

Subject: Confused on how to approach problem…

Jonathan,The best strategy is always to take “raw” data from Notes to Excel and do the Calculations taht you want on the Spreadsheet itself once that data has been placed there.

Therefore, you have two calculations for any data set:

  1. The Total amount(s) derived on the View (in Notes)

  2. The Total amount(s) calculated by very easy formulas on the Excel spreadsheet.

You can do some neet things with Excel as well, you don’t need to restrict yourself to doing all the calculations in Notes.

Regards

Rolf Pfotenhauer