Customized histogram

[OlavOhmark]

Hello,

I have a signal (UAValue) that I want to present as a kind of histogram, but in a line diagram rather than a bar chart. The histogram should be based on a second signal (Power). I have created a standard histogram i Seeq like this (disregard from the monthly aggregation for the moment):

Each bin is 500 units wide.

The diagram I want to have looks like this (created in Power BI):

 

I have got as far as creating a signal (Power_I) where the values are rounded to nearest 500, but I'm stuck on how to create a signal where an average of UAValue is calculated for each value in Power_I. I also want to aggregate on year (or rather heating season, July to June) and add a reference curve.

What is the best approach to achieve this?

[Sharlinda Salim]

Hi Olav,

There is a workaround in XY Plot but it may not able to plot exactly the same as your Power BI screenshot.

Step 1: Create the binning conditions using value search. As an example I created 4 conditions with bin size of 10.

 

Step 2: Create the condition for June and July

join(
months(Month.June, "Asia/Kuala_Lumpur"),
months(Month.July, "Asia/Kuala_Lumpur"),90d)

Step 3: Calculate the average value during each bin in June&July and combine them together. The move is to avoid overlap at the same startkey.

combinewith(
$t.within($b010).aggregate(average(),$june_july,startKey(),0s).move(1h),
$t.within($b102).aggregate(average(),$june_july,startKey(),0s).move(2h),
$t.within($b203).aggregate(average(),$june_july,startKey(),0s).move(3h),
$t.within($b304).aggregate(average(),$june_july,startKey(),0s).move(4h))

Step 4: Used the setproperty() to create the corresponding X value for each of the average value.

combinewith(
$t.within($b010).aggregate(average(),$step2,startKey(),0s).move(1h).toCapsules().setProperty('Bin',10),
$t.within($b102).aggregate(average(),$step2,startKey(),0s).move(2h).toCapsules().setProperty('Bin',20),
$t.within($b203).aggregate(average(),$step2,startKey(),0s).move(3h).toCapsules().setProperty('Bin',30),
$t.within($b304).aggregate(average(),$step2,startKey(),0s).move(4h).toCapsules().setProperty('Bin',40))
.toSignal('Bin',startKey()).toDiscrete()

Step 5: Switch to the XY Plot and expand the date range. Create the individual conditions for each year and you can then color based on these conditions.

Let me know first is this workaround suits you.