topic Re: Null appears as Zero when using inside Sum Function in App Development

Null appears as Zero when using inside Sum Function

anushree1 — Mon, 09 Sep 2019 10:43:11 GMT

HI,

I am creating YOY Sales in the Backend using PEEK function to get the Previous Year Value.

This works fine, the first entry of the Year appears as'-' in the data model for PrevYr as expected.

But, when i create a chart basis this the PrevYr ,it appears as Zero on the First Year though it is supposed to be '-' when i use it inside Sum like Sum(PrevYr) but , if use just PrevYr as the expression it results with '-'

I would like to get '-' for the first entry as there is no previous year for it , even if i use Sum function.

Can someone please suggest how this could be done

Re: Null appears as Zero when using inside Sum Function

StarinieriG — Mon, 09 Sep 2019 12:13:53 GMT

you could try with something like this:

If(Len(PrevYr )=0, Null() , Sum(PrevYr ))

Re: Null appears as Zero when using inside Sum Function

anushree1 — Mon, 09 Sep 2019 14:50:47 GMT

This solution would not work as i will need to use If(Len(sum(PrevYr ))=0, Null() , Sum(PrevYr )) as in case there are no selection an aggregation like sum is essential

Re: Null appears as Zero when using inside Sum Function

anushree1 — Mon, 09 Sep 2019 14:52:27 GMT

Any suggestions @Vegar , @kaushiknsolanki

Re: Null appears as Zero when using inside Sum Function

kaushiknsolanki — Tue, 10 Sep 2019 04:58:39 GMT

Hi,

Can you share your app with sample data.

Regards,

Kaushik Solanki

Re: Null appears as Zero when using inside Sum Function

anushree1 — Tue, 10 Sep 2019 06:21:01 GMT

Sure,

please find the attached,

I need the first row with 2011 as Year to have '-' for Grp.YOY column as there is no row above it.

Re: Null appears as Zero when using inside Sum Function

marcus_sommer — Tue, 10 Sep 2019 14:06:09 GMT

What's about: If(Len(concat(PrevYr, ','))=0, Null() , Sum(PrevYr )) ?

- Marcus

Re: Null appears as Zero when using inside Sum Function

kaushiknsolanki — Tue, 10 Sep 2019 15:36:20 GMT

Try this.

=if(RowNo()=1,null(), Sum([Grp.YOY]))

The reason it is not coming is because you have created that field in script.

If you use below expression in chart it will show you null by default.

Above(Sum(Actual))

Regards,

Kaushik Solanki

Re: Null appears as Zero when using inside Sum Function

anushree1 — Wed, 11 Sep 2019 05:48:12 GMT

@kaushiknsolanki , the solution would not work as we are specifically using Rowno() and making the first appear as null,

But this needs to be dynamic in case i add another field like Div the First on each entry of Div must show me '-'

Re: Null appears as Zero when using inside Sum Function

anushree1 — Wed, 11 Sep 2019 08:26:47 GMT

Hi,

The solution works well, But when I sort i see the '-' coming before the negative values , For Eg , is it possible to move 2011 at the bottom after showing negative values.

Snapshot:

Attached is the sample app

Re: Null appears as Zero when using inside Sum Function

marcus_sommer — Wed, 11 Sep 2019 08:57:47 GMT

I'm not sure if it's possible at least not without any ugly side-effects ... you may use:

If(Len(concat(PrevYr, ','))=0, dual('-', 0) , Sum(PrevYr ))

If(Len(concat(PrevYr, ','))=0, dual('-', min/max(total aggr(Sum(PrevYr ), Dim1, Dim2)) +-1) , Sum(PrevYr ))

but it will for example make trouble in totals and might be there calculated different - maybe by querying the levels with dimensionality() or ...

- Marcus

Re: Null appears as Zero when using inside Sum Function

anushree1 — Wed, 11 Sep 2019 09:53:09 GMT

it is not working

Re: Null appears as Zero when using inside Sum Function

marcus_sommer — Wed, 11 Sep 2019 10:18:53 GMT

You could not take the suggestion 1:1 because it's just a generic example to show which various measures might be useful. It needs the appropriate adjustments to your data-structure, fieldnames and a valid syntax.

- Marcus