topic Re: First Non-zero value in a chart in QlikView

First Non-zero value in a chart

Anonymous — Tue, 07 Feb 2017 21:59:20 GMT

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.

Re: First Non-zero value in a chart

jayanttibhe — Wed, 08 Feb 2017 01:07:30 GMT

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

];

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 01:50:33 GMT

Can you share a sample app to test this out?

Re: First Non-zero value in a chart

Anonymous — Wed, 08 Feb 2017 02:46:20 GMT

Hi Jayant,

This needs to be done in the chart.

Re: First Non-zero value in a chart

Anonymous — Wed, 08 Feb 2017 15:24:02 GMT

Attached it.

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 16:13:06 GMT

May be this?

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

Re: First Non-zero value in a chart

Anonymous — Wed, 08 Feb 2017 16:20:18 GMT

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

Re: First Non-zero value in a chart

jayanttibhe — Wed, 08 Feb 2017 16:23:48 GMT

Check attached- is it something you are looking for ?

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 16:25:52 GMT

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

Re: First Non-zero value in a chart

jayanttibhe — Wed, 08 Feb 2017 16:31:23 GMT

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.

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 16:34:26 GMT

Again this is just a sample and the actual expression might be more complex (instead of Sum(Value) if it was Sum(Aggr(FirstSortedValue(Aggr(Sum(Value), Date), -Date), Date, OtherDimensions)) or something)... would you still want to do this in the backend?

Re: First Non-zero value in a chart

Anonymous — Wed, 08 Feb 2017 16:37:57 GMT

Thank you both. Client's requirement was to do it on chart level. But thank you for your input.

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 16:45:07 GMT

This?

Expression:

If(Sum(Value) <> 0, Sum(Value), Above(TOTAL Column(1)))

Re: First Non-zero value in a chart

jayanttibhe — Wed, 08 Feb 2017 17:32:13 GMT

Depends on the requirement; If target expression where he needed non zero values of row above look like the one you have shown, I would first get additional field with that complex expression and then would apply the same logic on that field

Re: First Non-zero value in a chart

sunny_talwar — Wed, 08 Feb 2017 17:38:00 GMT

Again, I don't think this is always a good idea... but if you think that's the best way... then ya that is always an option

Re: First Non-zero value in a chart

Anonymous — Thu, 09 Feb 2017 02:10:39 GMT

Thank you Sunny, looks like Jayant's solution is close to what client is looking for.

Re: First Non-zero value in a chart

sunny_talwar — Thu, 09 Feb 2017 02:11:47 GMT

Sounds good... I thought your expression was complicated and that is why you wanted to do this on the front end. If that's what you need. I suggest marking his response as correct and close this thread

Best,

Sunny

Re: First Non-zero value in a chart

Anonymous — Thu, 09 Feb 2017 02:14:28 GMT

Ideally, was looking for only front-end option but seems like there's a compromise on it now.

Re: First Non-zero value in a chart

sunny_talwar — Thu, 09 Feb 2017 02:16:23 GMT

That's great.... Backend should always be preferred over front end. Wise decision