First Non-zero value in a chart

[Anonymous]

Sample file attached. Expectation is to have in FinalResult column, first non-zero value from the grouping if Sum is zero:

Cat2	=Cat1&'-'&Cat3	Sum(Value)	Final Result
1	A-X	28.2	28.2
2	A-X	29.3	29.3
3	A-X	0	29.3
4	A-X	0	29.3
5	A-X	0	29.3
1	A-Y	22.6	22.6
2	A-Y	21.9	21.9
3	A-Y	0	21.9
4	A-Y	0	21.9
5	A-Y	27.6	27.6
1	B-X	23.4	23.4
2	B-X	29.2	29.2
3	B-X	0	29.2
4	B-X	23.8	23.8
5	B-X	25.7	25.7

Above() only works for one-level above. Trying to see if FristSortedtValue() would work.

Thank you.

[jayanttibhe]

You can handle it in script like:

TEST11:

Load Cat1,Cat2,

If(Val= 0, Rangesum(Val,Peek(Val)),Val) As Val

;

LOAD * INLINE [

    Cat1, Cat2, Val

    1, A, 28.5

    1, B, 29.3

    1, C, 27.8

    1, D, 0

    1, E, 0,

    1, F, 0

    2, A, 22.6

    2, B, 0

    2, C, 21.9

];

[sunny_talwar]

Can you share a sample app to test this out?

[Anonymous]

Hi Jayant,

This needs to be done in the chart.

[Anonymous]

Attached it.

[sunny_talwar]

May be this?

If(Sum(Value)<>0,Sum(Value), Above(Column(2)))

[Anonymous]

This is going to be a line chart eventually and column(2) won't be there. Any other options ?

[jayanttibhe · Accepted solution]

Check attached-  is it something you are looking for ?

[sunny_talwar]

I guess he need this specifically on the front end and that could be because of use of a complicated expression

[jayanttibhe]

Well Sunny - I would prefer to do it in backend. As if sorting order on front end changed then the solution will not work . What do you think ?

Moreover, its not difficult to built in backend.