Step chart showing rogue values across multiple days

user06072

I'm charting event based data using a step line (chart).   If looking at a week of data, the chart is filling in the line from midnight (start of the day) to the first recorded data point on that day.   I'd prefer it to continue the value from the prior day through until the event.   For example, on Wednesday the last value of "0" was seen at 8pm.   On Thursday, the first value was "100" at 7am.   Therefore, I'd like to see a line between 8pm and 7am of "0".   Right now, it shows "0" from 8pm to midnight and then "100" from midnight to 7am.   See attached pic.   Thoughts?

 

 

 

jaeW_at_Onyx

so you want to do a cumulative sum?

if your answer is 'no my data doesn't support that', restructure your data so you can do a cumulative sum. then you could do what you want with a window function.  sum(sum(amount)) over (partition by week)

user06072

Thanks for the reply, but unfortunately "my data doesn't support that".   *smiling*

 

I will backup my statement though...   The data is driven by events that happen.   Think a motion detector logging when an office sees motion.   This could happen once a second or only once across multiple days;   event driven.   I could expand the dataset to have a row for every second of the day and express that as a "true/false" for the data, but that would lead to 86,400 rows/day/sensor.   My building has about 40 sensors in it...   resulting in 3.4M rows per day.   Not gonna work.

 

Is there not a way to format a step chart where time is the x-axis and it will not "fill" in from midnight to the first data point for that day?

 

Thanks.

jaeW_at_Onyx

i'm still unclear what you're trying to report.

 

you could just add a row to your dataset for the last recorded value of each day and set the time for midnight.

 

or instead of dividing your dat into seconds ... aggregate it into minutes or 15-minute blocks.  at the level you're visualizing your data at, this high granularity is unecesary

 

user06072

I'm looking to have a chart that shows actual events that took place.   The events take place at a particular time (down to the second).   In some cases, a resolution of a second is necessary.   If the chart does not reflect "real" data, then hoovering over a data point would display incorrect data.   I like the idea of adding a row at midnight, but that would create a data point on the chart that would not be accurate...   no motion was detected at that time.

MarkSnodgrass

I would consider using a different type of card for this type of visualization. Have you tried using a Scatter card and using the time as the x-axis?