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.
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.
I'm not very used to the set expressions yet. Thus, I'm adding yet another question to expand this model .
In a new column next to the sum column you showed above, I would like to see the sum untill month before. If I select 2011-08-01 in your attached application, I get the sum 6000, and if I select 2011-07-01 I get 5400. I would like 2 columns, one showing the 6000 and one showing 5400.
I understood that both expressions work with your sample data and only the sum until last month works not correctly with your data, the other one does, right?
If you don't use a label in your expression, your will get the expression printed as label, but with the set expression evaluated (so you see the formatted date). If you select any month, what Dates do you see in the set expression? Do they seem reasonably (the format should match the format of your Date field)?
Is anything different in your real data model compared to above sample (except Date format)? If so, could you adjust your sample to match it?
both are working with the sample, but the latter one (month -1) does not work with my real data.
Those are the 2 expressions I get when not adding a lable and selecting 20111101, where the first one is just fine and the second one is ok when not selecting a month. The date format seems to be just fine from what I can say.
The only thing that comes to my mind is that there are 3 different tables (1 for calendar data, 1 for invoice data and 1 for general project information data). However, since it's in the expression and not the script, this shouldnt affect (?), and both expressions are ok, eventually.