MAX 'Day' per Month - How to do it right??

[#1]

Hi,

i have a database which has one value per Month. I want to create a table with dimension "month", which shows the value of the last day of the month (e.g. January -> value of 31.01).

I have all type of time dimenions (Yearmonth, Month, Day, ....)

The result should look like

2010-01      |      1.000

2010-02      |       376

2010-03      |       876

....

Is there a possiblity to use a SIMPLE formula??

The Formula [ SUM ({$<Day= {$(=max (Day))}> Value) ] is only showing results for months with the date 31.xy ...

At the moment i use the following formula, which is a ugly one:

if ( C3_VERTRAEGE_MONTH_OF_YEAR=(01),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(02),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'28'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(03),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(04),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(05),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(06),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(07),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(08),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(09),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(10),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(11),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),

sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE)          )))))))))))))))))))))

[swuehl]

I assume that you also have a Date field (and you use a Month type field as dimension (Year-Month, Month ...)):

=SUM ( if(Date= monthend(Date), Value) )

Hope this helps,

Stefan

[#3]

Hi Stefan,

unfortunately it is not working....

even if i explain my DateIDs as date there is only 0 as result

- >= SUM ( if ( date (DATEID, 'YYYY-MM-DD') = montend (date (DATEID, 'YYYY-MM-DD')), VALUE ))

do you have an idea?

Tank you very much

kind regards

Jezze

[swuehl]

Ah yes, try a

sum( if (DATEID = daystart(monthend(DATEID)), VALUE))

monthend will return a  numerical representation for the last millisecond of the month,

like '2011-12-31 23:59:59.999'

And I assume you DATEID are not time stamps.