Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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).
@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:
Could you please provide me condition to be written in expression editor rather than in script editor?
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: