dimenstion month -- max(date) -- and then I only want the value of this max(date)
I have a table which looks something like the following:
Configuration.ID Configuration.Date, Configuration.Year, Configuration. Month, Configuration.Week, Configuration.Number, Amount 5 9-6-2010 2010 jun 23 26 5 1-6-2010 2010 jun 23 24 5 6-5-2010 2010 may 18 20 5 21-4-2010 2010 apr 16 8 7 9-6-2010 2010 jun 23 22 7 6-5-2010 2010 may 18 17 7 21-4-2010 2010 apr 16 16
I want to show the installed base (Amount) for each year and each month.
The installed base amount is the max(date) of that month (or year) -- and the amount for that day is taken (over all configuration Id's)
So over months it would result in:
2010 - apr - 24 2010 - may - 37 2010 - jun - 48
so I thought of something like:
sum({<ConfigurationManagement.Date= {"$(=Max(ConfigurationManagement.Date))"} >} total <ConfigurationManagement.Date> ConfigurationManagement.Amount)
This gives the 'current' installedbase ...
Is it a good idea of doing this all at the back-end (loading script)?? Now I'm working for a solution at this point ... think this is the only way of solving this quickly ...
I'm not even going to try to write the code on the fly, it would be riddled with syntax problems.
I would appraoch it by loading your table a second time, grouping by the month, and taking the max(amount); I'd do it again, grouping by the year field.
I'm not sure what you intend to do with the result. You could either create a summary table, or you could join it in to your existing one.
