Null appears as Zero when using inside Sum Function

[anushree1]

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

[StarinieriG]

Hi

you could try with something like this:

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

[anushree1]

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

[anushree1]

Any suggestions @Vegar , @kaushiknsolanki 

[kaushiknsolanki]

Hi,

Can you share your app with sample data.

Regards,

Kaushik Solanki

[anushree1]

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.

[marcus_sommer · Accepted solution]

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

- Marcus

[kaushiknsolanki]

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

[anushree1]

@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 '-'

[anushree1]

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