Show accumulated values in line chart, only until current hour

[pedrohenriqueperna]

Hi,

I have a line chart that shows sales values separated in time intervals. The values are accumulated, for which I've used the accumulation modifier. The dash updates every hour and the ideia is to display the sales values per hour, showing the total for each hour. It kinda works as expected, but it repeats the cumulation for every hour in the future. How can I avoid the repeated values?

The red line should display 86,67k only the first time. You can see it repeats the value for the future hours.

I've tried something like comparing the hour dimension with the current time saved in a variable, but weird things happen, I'm not sure what and why, but the idea was something like "if current time is < than time_dimension, display null(). I'm using continuous scale, because it's the only way I found to make the intervals work along with the sales timestamp, I'm not sure if that could be the problem...

This is the expression I'm currently using:
(
Sum({1<erp_hora_lancamento = {"<=$(=Time(MakeTime(Hour(Max({1<flag_venda_devolucao = {'V', 'D'}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}>} erp_hora_lancamento)), 59, 59)))"}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}, flag_venda_devolucao = {'V'}>} erp_valor_total)
-
Sum({1<erp_hora_lancamento = {"<=$(=Time(MakeTime(Hour(Max({1<flag_venda_devolucao = {'V', 'D'}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}>} erp_hora_lancamento)), 59, 59)))"}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}, flag_venda_devolucao = {'D'}>} erp_valor_total)
)

We analyse both sales and devolutions, that's the reason I subtract both.

flag_venda_devolucao = flag to identity whether it's a sale or a devolution.

erp_hora_lancamento = the sale timestamp

Basically, I get the time for the latest sale/devolution to set the time scope in the expression. Not sure if it's the best expression, but it works without the cumulation.

How can I display only 1 value for the latest hour, instead repeating the same value?

[marcus_sommer]

By using the default chart-settings your if-loop approach should be working but extra settings like the continuous scale or accumulation may overwrite the normal behaviour.

An alternatively might be to disable those features and using interrecord-functions to get the accumulation, maybe something in this way:

rangesum(above(YourExpression, 0, rowno(total))) * count(distinct Hour)

[pedrohenriqueperna]

Hi, Marcus

I was tweaking around the additional options like continuous scale, but it turns out that the problem was something with the expression itself and how my dimensions are formatted. The following expression did the job:

If(Hour(erp_hora_intervalo) <= '$(=Hour(vNow))' and Hour(erp_hora_intervalo) <> 0,
RangeSum(
Above(
(
(
Sum({<erp_hora_lancamento = {"<=$(=Time(Floor(TimeStamp#(vNow, 'mm/dd/yyyy h:mm:ss TT'), 1/24), 'hh:mm:ss'))"}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}, flag_venda_devolucao = {'V'}>} erp_valor_total)
-
Sum({<erp_hora_lancamento = {"<=$(=Time(Floor(TimeStamp#(vNow, 'mm/dd/yyyy h:mm:ss TT'), 1/24), 'hh:mm:ss'))"}, erp_data_lancamento = {"$(=Date(Today(), 'YYYY-MM-DD'))"}, flag_venda_devolucao = {'D'}>} erp_valor_total)
)　

), 0, RowNo(Total)))
)

Thanks for helping.