cancel
Showing results for
Did you mean:
New Member

## Previous Value by Device & Time

Hello Everybody,

i've spent the whole afternoon with this dataset:

 mts DeviceID cOut 19/10/2020 00:00 A 16.4 19/10/2020 00:05 A 16.4 19/10/2020 00:10 A 16.4 19/10/2020 00:15 A 16.4 19/10/2020 00:20 A 16.3 19/10/2020 00:25 A 16.2 19/10/2020 00:30 A 16.2 19/10/2020 00:35 A 16.2 19/10/2020 00:40 A 16.2 19/10/2020 00:45 A 16.2 19/10/2020 00:00 B 16.2 19/10/2020 00:05 B 16.1 19/10/2020 00:10 B 16.1 19/10/2020 00:15 B 16.1 19/10/2020 00:20 B 16.0 19/10/2020 00:25 B 16.0 19/10/2020 00:30 B 15.9 19/10/2020 00:35 B 15.9 19/10/2020 00:40 B 15.9 19/10/2020 00:45 B 15.9

That I want to transform in this view

 mtsHour Device Average of cOut Difference 19/10/2020 00:00 A 20,72 19/10/2020 01:00 A 20,70 -        0,02 19/10/2020 02:00 A 20,70 0,00 19/10/2020 03:00 A 20,62 -        0,08 19/10/2020 04:00 A 20,53 -        0,10 19/10/2020 05:00 A 20,58 0,05 19/10/2020 00:00 B 20,57 19/10/2020 01:00 B 20,54 -        0,03 19/10/2020 02:00 B 20,35 -        0,19 19/10/2020 03:00 B 20,08 -        0,27 19/10/2020 04:00 B 20,57 0,49 19/10/2020 05:00 B 20,66 0,09

What I need, in few words, is to have a new column that calculates the Difference between the AVERAGE of cOut (Temperature) between an Hour and another.

If too complicated I can also work on the non-grouped by mtsHour dataset and get a value for a single row so I can aggregate it later.

I've tried a ton of samples but no-one really works.

Thank you for your help! Will be very appreciated!

Max

1 ACCEPTED SOLUTION
Super User IV

@MaxEle , not sure how got this avg of 20,

Try a new column

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(table, [Device] = earlier([Device]) && [mtsHour] <earlier([mtsHour])))

or new measure

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(allselected(table), [Device] = max([Device]) && [mtsHour] <max([mtsHour])))

Proud to be a Super User!

2 REPLIES 2
Community Support

Hi, @MaxEle
It will help other community members easily find the solution when they get the similar issue.
For now, there is no content of description in the thread. If you still need help, please share more details to us.

Best Regards,
Community Support Team _ Eason

Super User IV

@MaxEle , not sure how got this avg of 20,

Try a new column

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(table, [Device] = earlier([Device]) && [mtsHour] <earlier([mtsHour])))

or new measure

calculate(lastnonblankvalue([mtsHour],max(Average of cOut)),filter(allselected(table), [Device] = max([Device]) && [mtsHour] <max([mtsHour])))

Proud to be a Super User!

Announcements