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

topic MAX 'Day' per Month - How to do it right?? in QlikView https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290901#M107998 <HTML><HEAD></HEAD><BODY><P>Ah yes, try a </P><P></P><P>sum( if (DATEID = daystart(monthend(DATEID)), VALUE))</P><P></P><P>monthend will return a  numerical representation for the last millisecond of the month,</P><P>like '2011-12-31 23:59:59.999'</P><P></P><P>And I assume you DATEID are not time stamps.</P></BODY></HTML> Fri, 09 Dec 2011 16:27:19 GMT swuehl 2011-12-09T16:27:19Z MAX 'Day' per Month - How to do it right?? https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290898#M107995 <HTML><HEAD></HEAD><BODY><P>Hi, </P><P></P><P>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).</P><P></P><P>I have all type of time dimenions (Yearmonth, Month, Day, ....)</P><P></P><P>The result should look like </P><P></P><P>2010-01      |      1.000</P><P>2010-02      |       376</P><P>2010-03      |       876</P><P>....</P><P></P><P></P><P>Is there a possiblity to use a SIMPLE formula??</P><P></P><P></P><P>The Formula [ SUM ({$<Day= {$(=max (Day))}> Value) ] is only showing results for months with the date 31.xy ...</P><P></P><P>At the moment i use the following formula, which is a ugly one:</P><P></P><P>if ( C3_VERTRAEGE_MONTH_OF_YEAR=(01), </P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE), </P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(02),</P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'28'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(03),</P><P> sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(04),</P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P> (if ( C3_VERTRAEGE_MONTH_OF_YEAR=(05),</P><P> sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P> (if ( C3_VERTRAEGE_MONTH_OF_YEAR=(06),</P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(07),</P><P> sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(08),</P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(09),</P><P> sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(10),</P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>(if ( C3_VERTRAEGE_MONTH_OF_YEAR=(11),</P><P> sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'30'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE),</P><P></P><P>sum({$< C3_VERTRAEGE_DAY_OF_MONTH={'31'}, C3_VERTRAEGE_MONTH_OF_YEAR ={"*"}>}  C3_CUMULATIVE)          )))))))))))))))))))))</P></BODY></HTML> Fri, 09 Dec 2011 15:14:35 GMT https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290898#M107995 2011-12-09T15:14:35Z MAX 'Day' per Month - How to do it right?? https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290899#M107996 <HTML><HEAD></HEAD><BODY><P>I assume that you also have a Date field (and you use a Month type field as dimension (Year-Month, Month ...)):</P><P></P><P>=SUM ( if(Date= monthend(Date), Value) ) </P><P></P><P>Hope this helps,</P><P>Stefan</P></BODY></HTML> Fri, 09 Dec 2011 15:30:34 GMT https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290899#M107996 swuehl 2011-12-09T15:30:34Z MAX 'Day' per Month - How to do it right?? https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290900#M107997 <HTML><HEAD></HEAD><BODY><P>Hi Stefan, </P><P></P><P>unfortunately it is not working....</P><P></P><P>even if i explain my DateIDs as date there is only 0 as result </P><P></P><P>- >= SUM ( if ( date (DATEID, 'YYYY-MM-DD') = montend (date (DATEID, 'YYYY-MM-DD')), VALUE ))</P><P></P><P>do you have an idea?</P><P></P><P>Tank you very much</P><P></P><P>kind regards</P><P>Jezze</P></BODY></HTML> Fri, 09 Dec 2011 15:59:26 GMT https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290900#M107997 2011-12-09T15:59:26Z MAX 'Day' per Month - How to do it right?? https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290901#M107998 <HTML><HEAD></HEAD><BODY><P>Ah yes, try a </P><P></P><P>sum( if (DATEID = daystart(monthend(DATEID)), VALUE))</P><P></P><P>monthend will return a  numerical representation for the last millisecond of the month,</P><P>like '2011-12-31 23:59:59.999'</P><P></P><P>And I assume you DATEID are not time stamps.</P></BODY></HTML> Fri, 09 Dec 2011 16:27:19 GMT https://community.qlik.com/t5/QlikView/MAX-Day-per-Month-How-to-do-it-right/m-p/290901#M107998 swuehl 2011-12-09T16:27:19Z