Sum from mindate to selected date

[#1]

Hi,

I have a list of projects which all have different start dates. I would like to make a "project to date" sum that works with the calendar function, meaning that if the start period is OCT-10 and I select 2011-06 in the calendar, I would like to see the sum from OCT-10 to 2011-06.

I have made a date out of period field OCT-10 (or similar) to look like 20101001 to include the calendar.

Example:

Project_ID	Date	Invoice	Cost
1	2010-10-01	A	500
1	2011-03-01	B	200
2	2011-04-01	C	2500
1	2011-05-01	D	400
2	2011-05-01	D	500
2	2011-06-01	E	1000
1	2011-07-01	F	300
1	2011-08-01	G	600
2	2011-09-01	H	1500
1	2011-11-01	I	100

From above table,if I in the calendar select 2011-08-01, I would like the sum(Cost) for each project from it's min(Date) to selected date. The function needs to work in a pivot table and chart as well, I would love to be able to pivot the Project_ID field and expand it to show the detaild level. *Update* Forgot to add field Invoice as a detaild level that holds information of the cost.

Any help would be appreciated!

BR

Robert

[#2]

Hi,

I got a bit closer, but don't know how to solve the problem. Attached is a picture (I will try to upload a sample if needed later).

First column should always show max cost of the fixed asset not matter what selections are done. Second column should show total cost to selected period. Third column shows total cost to selected period - 1, and fourth column should just show the change between column 2 and 3. What I've asked for is column 2 and 3.

Anyhow, there are 3 tables, as described above. If I select a period/date from field (reffering to attached pic) "PERIOD_NAME" or "FADate", which both are found in the general infromation table, column 2 is just showing current month value and column 3 is showing 0. I don't mind this since I'm not planning to present either of those fields.

If I select in "Year Month" which is the calendar table, I get the same as above, current month and 0.

If I select from "EffectivePeriod" which is in the invoice table, I get the same again.

However, If I select from "EffectiveDateFromPeriod", which is also in the invoice table, the two expressions are working perfectly fine. This field is also the timefield used in the calendar and the link between those tables.

Any idea how I can get the calendar working instead of the "EffectiveDateFromPeriod" field? No matter where I select in any of the mentioned fields above, Qlikview reduces the data in "EffectiveDateFromPeriod", basically passively selecting in that field. The expressions are however only working when actively selecting in that field.

BR

[swuehl]

I believe you need to clear all fields that the user might select in and that interfere with EffectiveDateFromPeriod:

sum({<Date= {"<=$(=EffectiveDateFromPeriod (addmonths(max(Date),-1),'YYYYMMDD'))"}, [Year Month]=, EffectivePeriod=, PERIOD_NAME=, FADate= >} FIXED_ASSETS_COSTS)

edit:

corrected some field names, but please double check!