Hi , I have the date as shown below:
| Date | MTDTOTAL |
|---|
| 01-01-17 | 10 |
| 02-01-17 | 4 |
| 03-01-17 | 15 |
| 01-02-17 | 6 |
| 02-02-17 | 20 |
| 03-02-17 | 0 |
| 01-03-17 | 11 |
| 02-03-17 | 8 |
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(rowno()=1,MTDTOTAl,
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.
| DAte | MTDTOTAL | MTDPEAKTOTAL | TEST |
|---|
| 01-01-17 | 10 | 10 | 10 |
| 02-01-17 | 4 | 10 | 10 |
| 03-01-17 | 15 | 15 | 15 |
| 01-02-17 | 6 | 6 | 15 |
| 02-02-17 | 20 | 20 | 20 |
| 03-02-17 | 0 | 20 | 20 |
| 01-03-17 | 11 | 11 | 20 |
| 02-03-17 | 8 | 11 | 20 |
