Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
Can you share the sample data at least for two metrics?
Hi,
Check the script in the attachment.
Regards,
David