topic Re: Nulls and Zeros in SUM function in App Development

Re: Nulls and Zeros in SUM function

panosalexand — Mon, 20 Nov 2023 14:17:37 GMT

Unfortunately, this didn't work for me. In my example, I try to show all the possible timeslots during the date.

The data are available from 07:00-09:30 and from 10:30 and so on. I'd like to show that for 10:00 it was zero.
In my sorting, data are coming for null values(look SORT = 7).

Is it a setting in the pivot chart or need to change my expression? ( sum(field) )

Thanks,

Re: Nulls and Zeros in SUM function

vincent_ardiet_ — Mon, 20 Nov 2023 15:20:45 GMT

Stupid question but are you sure that the Suppress Zero Values or Suppress Missing options are not checked?

Re: Nulls and Zeros in SUM function

panosalexand — Mon, 20 Nov 2023 15:24:29 GMT

Isn't a stupid question, but yes, options are not checked.
Most probably I have to change the expression in order to get the values without data but I still struggle to find the right.

Re: Nulls and Zeros in SUM function

vincent_ardiet_ — Mon, 20 Nov 2023 15:27:11 GMT

And have you a filter currently applied? I haven't noticed that SORT 7 is grayed out in the listbox, so it is not available in the current context.

Re: Nulls and Zeros in SUM function

panosalexand — Mon, 20 Nov 2023 15:42:53 GMT

Exactly! I've applied a filter on a particular day. On this day we have production records from 7 am to 7 pm.
If there isn't a record I need to know this time.

Is something similar to the date. eg You need to know sales for the current year. If you don't have sales for June you will not get the gap and maybe you will not understand it. For this reason you have to apply a master calendar.

Re: Nulls and Zeros in SUM function

vincent_ardiet_ — Mon, 20 Nov 2023 15:55:51 GMT

In this case try something like this:
Rangesum(sum(field),sum({1} 0))

Re: Nulls and Zeros in SUM function

panosalexand — Tue, 21 Nov 2023 07:46:58 GMT

Vincent many thanks!! Is working!!!

Re: Nulls and Zeros in SUM function

panosalexand — Tue, 21 Nov 2023 13:15:13 GMT

Vincent, I try to apply the same for AVG.

Redline just a simple average which is the correct --> avg(Field1)

The blue line is what you suggested but with AVG. In this case, I have all the range but the values are approximately half --> Rangeavg(avg(Field1),avg({1} 0))

Do you have any idea?

Re: Nulls and Zeros in SUM function

vincent_ardiet_ — Tue, 21 Nov 2023 13:29:04 GMT

Yes, because RangeAvg is doing the average between all arguments.
Keep RangeSum in this case too :
RangeSum(avg(Field1),avg({1} 0))

Re: Nulls and Zeros in SUM function

panosalexand — Tue, 21 Nov 2023 13:49:05 GMT

Thanks for the immediate response