Comparing alarm of a period and previous period

[#1]

Hi,

I would like to know how to refer to a variable from the previous perido. The variable period is defined as a number: 1..36 (like months).

The table is like the following:

customerId	period	alarm
4	26	0
4	27	2
4	28	0
4	29	3
4	30	0
4	31	1
4	32	1
4	33	0
4	34	0
4	35	0
4	36	0

I want to show in Qlik the following table

customerId	period	alarm	prevAlarm	Difference
4	27	2	0	1
4	28	0	2	0
4	29	3	0	1
4	30	0	3	0

How could I calculate prevAlarm? Difference is 1 if the current alarm >0 and the previous was <=0. I have the insight of how to do this, but I need to refer to prevAlarm.

Thank you,

[kaushiknsolanki · Accepted solution]

Try this.

Load *, if(alarm >0 and  PrevAlarm <=0, 1,0) as Difference;

Load CustomerID, Period, alarm, previous(alarm) as PrevAlarm

From xyz;

Regards,

Kaushik Solanki

[vvira1316]

Hi,

You will have to change logic for more than one customerId in your data

NoConcatenate
AlarmTmp:
LOAD customerId
,period
,alarm
FROM
[..\Data\Test12.xlsx]
(ooxml, embedded labels, table is Sheet1);
//order by customerId desc, period desc;

NoConcatenate
AlarmData:
LOAD customerId
,alarm as prevperiodalarm
,period as prevperiod
,Peek(alarm, RowNo(), 'AlarmTmp') as periodalarm
,Peek(period, RowNo(), 'AlarmTmp') as period
// ,If(RowNo()=1, Null(), Peek(alarm, RowNo(), 'AlarmTmp')) as periodalarm
// ,If(RowNo()=1, Null(), Peek(period, RowNo(), 'AlarmTmp')) as period
Resident AlarmTmp;
//order by customerId desc, period desc;

DROP Table AlarmTmp;

expression

=If(periodalarm > 0 and prevperiodalarm <= 0, 1, 0)