Comparing the two dates and getting max value(MTD)
Hi , I have the date as shown below:
The expected result is as shown below in MTDPEAKTOTAL column. But what I am getting is as shown in the TEST Coulmn wherein I am using the expression as Rangemax(above(total sum(MTDTOTAL),0,rowno(TOTAL)) i.e when it encounter next month it should start comparing with the first day value of that month which is not happening.
I tried doing in backend using the peek function
if(MONTHYEAR = PEEK(MONTHYEAR) and MTDTOTAL>PEEK(MTDPEAKTOTAL),MTDTOTAL,PEEK(MTDPEAKTOTAL)) as MTDPEAKTOTAL. This expression works completely fine but the problem is I have 10 different metrics for each metrics it has to do. So at each metric level it is not coming correctly. Kindly guide me suitable Suggestion. Thanks in Advance.