Comparing the two dates and getting max value(MTD)

nikithaj — Wed, 12 Apr 2017 08:26:43 GMT

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

Re: Comparing the two dates and getting max value(MTD)

Kushal_Chawda — Wed, 12 Apr 2017 13:51:26 GMT

Can you share the sample data at least for two metrics?

Re: Comparing the two dates and getting max value(MTD)

daveamz — Wed, 12 Apr 2017 14:42:26 GMT

Hi,

Check the script in the attachment.

Regards,

David

topic Comparing the two dates and getting max value(MTD) in QlikView

Comparing the two dates and getting max value(MTD)

Re: Comparing the two dates and getting max value(MTD)

Re: Comparing the two dates and getting max value(MTD)