Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Dear community,
We have sensors at productionlines which measure the number of products passing by. Every noon they reset the total number for the day. Every minute it gives the new total for the day so far i.e. 14:01:00 = 100654, 14:02:00 = 100676. Therefore I can't use the SUM-function in analysis and I'm using the MAX-function. The problem with this, I can't zoom in to hours or minutes and can't zoom out for weeks, months and further (i.e. It will give the MAX value for the week, when it has already been reset six times at least)
Can I use a combined MAX and SUM function in the Analysis part? Or should I make a new column in the table which calculates the difference between two measures from the sensors? (in my mind this is the simplest way) I thought about the IF function like MS Excel:
=IF(condition;if_value_is_true;if_value_is_false)
=IF(measureB1>measureB2;0;measureB2-measureB1)
This leaves the negative values out (when the systems resets).
Which is the simplest way to do a correct measure and how to calculate it?
Thanks in advance.
Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.
You can get the idea by examening the script below.
//Assuming dates in format 'YYYY-MM-DD'
RawData: LOAD *,
DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon
frac( Time + 0.5 ) as LogRelativeTime //Time passed from noon Inline [ Date, Time, Measure 2019-04-15, 14:02:00, 100676 2019-04-15, 14:01:00, 100654 2019-04-15, 11:02:00, 200676 2019-04-15, 11:01:00, 200654 2019-04-14, 14:02:00, 100677 2019-04-14, 14:01:00, 100653 2019-04-14, 12:00:00, 3 2019-04-14, 11:02:00, 200677 2019-04-14, 11:01:00, 200653 2019-04-13, 14:02:00, 100678 2019-04-13, 14:01:00, 100656 ]; LOAD LogDate, Date, Time, Measure as [Day total acc Measure], //Your old value if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure Resident RawData ORDER BY LogDate asc, LogRelativeTime asc; Drop table RawData; exit script
I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table.
Have you considered calculating relative amounts in your data. for every row calculate the delta from the previous row within the reset cycle.
You can get the idea by examening the script below.
//Assuming dates in format 'YYYY-MM-DD'
RawData: LOAD *,
DayName(Date+Time,-1,0.5) as LogDate , //The file resets every day at noon
frac( Time + 0.5 ) as LogRelativeTime //Time passed from noon Inline [ Date, Time, Measure 2019-04-15, 14:02:00, 100676 2019-04-15, 14:01:00, 100654 2019-04-15, 11:02:00, 200676 2019-04-15, 11:01:00, 200654 2019-04-14, 14:02:00, 100677 2019-04-14, 14:01:00, 100653 2019-04-14, 12:00:00, 3 2019-04-14, 11:02:00, 200677 2019-04-14, 11:01:00, 200653 2019-04-13, 14:02:00, 100678 2019-04-13, 14:01:00, 100656 ]; LOAD LogDate, Date, Time, Measure as [Day total acc Measure], //Your old value if(peek('LogDate')=LogDate, Measure - Peek('Day total acc Measure'), Measure) as Measure Resident RawData ORDER BY LogDate asc, LogRelativeTime asc; Drop table RawData; exit script
I get an error while trying to change "measure" to my own column. I want to do it for all eight measures (Dutch: 'metingen'). Have I made an mistake? I've also added the coding for the original table.
(Datum-Tijd translates to Date-Time). Almost there! The function "drop table" doesn't work. Do you know where I went wrong this time?