Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Make your voice heard! Participate in the 2020 Wisdom of Crowds® Survey. BEGIN SURVEY
Highlighted
Roisin_Cooke19
New Contributor

Finding the difference between values based on month

Hi 

I have a column containing sensor values at the start of each month.

e.g. 

equipmentmonthsensor value
001jan-20191000
001feb-20191100
001mar-20191150
002jan-20192000
002feb-20192250
002mar-20192600

 

I want to obtain the difference between the sensor value of each month for each equipment.

The results should be as follows:

equipmentmonthmonthly hours
001jan-2019100
001feb-201950
001mar-2019-
002jan-2019250
002feb-2019350
002mar-2019-

 

Does anyone know how to do this in the data load editor?

Thanks,

Roisin

 

1 Solution

Accepted Solutions
Highlighted
MVP
MVP

Re: Finding the difference between values based on month

Try like:

t1:
LOAD equipment, 
     date#(month, 'MMM-YYYY') as month, 
     [sensor value]
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Finding-the-difference-between-values-based-on-month/td-p/1674704]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

t2:
Load
	*,
	If(equipment=peek(equipment), RangeSum(-[sensor value],peek([sensor value]))) as [monthly hours]
Resident t1 order by equipment, month Desc;

Drop Table t1;	

View solution in original post

2 Replies
Highlighted
MVP
MVP

Re: Finding the difference between values based on month

Try like:

t1:
LOAD equipment, 
     date#(month, 'MMM-YYYY') as month, 
     [sensor value]
FROM
[https://community.qlik.com/t5/New-to-Qlik-Sense/Finding-the-difference-between-values-based-on-month/td-p/1674704]
(html, utf8, UserAgent is 'Mozilla/5.0', embedded labels, table is @1);

t2:
Load
	*,
	If(equipment=peek(equipment), RangeSum(-[sensor value],peek([sensor value]))) as [monthly hours]
Resident t1 order by equipment, month Desc;

Drop Table t1;	

View solution in original post

Highlighted
Roisin_Cooke19
New Contributor

Re: Finding the difference between values based on month

Thank you very much for your help Tresesco, that worked perfectly! 

Regards,

Roisin