Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
I think you could use a set expression like
=sum({<Date= {"<=$(=max(Date))"}>} Cost)
for this, in combination with a dimension Project_ID.
See also attached.
Hi,
that worked perfectly fine! Thak you very much!
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.
BR
Robert
Just add another expression:
sum({<Date= {"<=$(=addmonths(max(Date),-1))"}>} Cost)
Regards,
Stefan
Hi,
it works in the test application, but in my build, the date format is 20110801 (instead of 2011-08-01). Is there a way to format the date in the set expression?
The two expressions looks like this when selecting 20110901
=sum({<Date= {"<=20110901"}>} Cost) |
=sum({<Date= {"<=2011-08-01"}>} Cost) |
BR
Robert
Date() function should do the job. Try
sum({<Date= {"<=$(=Date(addmonths(max(Date),-1),'YYYYMMDD'))"}>} Cost)
The expression works when not selecting any month (it is second last months value), but when selecting, I get 0 value. Any idea?
Not yet...
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?
Hi,
both are working with the sample, but the latter one (month -1) does not work with my real data.
=sum({<FADate= {"<=20111101"}>}FIXED_ASSETS_COST) |
=sum({<FADate= {"<=20111001"}>}FIXED_ASSETS_COST) |
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.
BR
Sorry, no idea yet. Could you upload a small sample?
(Upload is available in advanced editor)