DIfference between maximum of previously noted month and a month using qliksense

[freceena]

I have an input file and is loaded into qliksense which is of the form shown in the attachment 'Input.png'. I have to get the output of the form 'Output.png'. The calculation is that, for Aug - 17 = no previous value so same value. For Sep-17 = max(Sep-17) - max (Aug - 17) and so on. For Feb -18 = max(feb-18) - max(Oct-17).

[Taoufiq_Zarra]

@freceena  One option:

Input:

LOAD TS_No,Date(Monthstart(Month),'MMM-YY') as Month,Value INLINE [
    TS_No, Month, Value
    TS1, 18.08.2017, 4115
    TS1, 14.09.2017, 4122
    TS1, 12.10.2017, 4133
    TS1, 01.02.2018, 19873
    TS1, 08.02.2018, 20404
    TS1, 23.02.2018, 23325
];


Tmp:
noconcatenate

load  TS_No,Month, max(Value) as Value resident Input group by TS_No,Month order by Month;

drop table Input;


output:
noconcatenate

load TS_No,Month,if(rowno()=1,Value,Value-peek(Value)) as New_Value,Value resident Tmp;

drop table Tmp;
drop fields Value;

output:

[freceena]

Could you please provide me condition to be written in expression editor rather than in script editor? 

[Taoufiq_Zarra]

@freceena 

Dimension:

=Date(Monthstart(Month),'MMM-YY')

Measure :

if(rowno() = 1,Max(Value),Max(Value)-above(Max(Value)))

and Sort by Month

Input:

LOAD * INLINE [
TS_No, Month, Value
TS1, 18.08.2017, 4115
TS1, 14.09.2017, 4122
TS1, 12.10.2017, 4133
TS1, 01.02.2018, 19873
TS1, 08.02.2018, 20404
TS1, 23.02.2018, 23325
];

output: