Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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?
Thank you both. Client's requirement was to do it on chart level. But thank you for your input.
This?
Expression:
If(Sum(Value) <> 0, Sum(Value), Above(TOTAL Column(1)))
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
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
Thank you Sunny, looks like Jayant's solution is close to what client is looking for.
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
Ideally, was looking for only front-end option but seems like there's a compromise on it now.
That's great.... Backend should always be preferred over front end. Wise decision