Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi all,
how to use Sum with First sortedvalue
I have to get sum of balance .. but the values is showing wrong with below expression.
=
sum(if (
isnull(FirstSortedValue([Balance], ssDate))
,
0
,
FirstSortedValue([Balance], ssDate)
)
)
Can anyone advice with correct syntax
Thanks
Chanty
Is the problem with Opening or Closing Balance? You are sharing the opening balance expression and pointing out that Closing balance is showing negative?
Okay let me explain.
the result is coming as "0" so the result is always result should be some amount as opening balance is not equal to zero.
the image showing the result per above expresion.
I see what you want, but I am not sure what might be missing. Is posting a sample a possibility bro?
No bro. that is the issue
Okay let me ask you this
I see that there are some values showing up and 0 are for totals? Is this true? May be we just need to figure out a way to fix the totals if the line level are already working
yes sunny.. now its clear check below image
How about this:
Sum(Aggr(If(IsNull(FirstSortedValue(Balance, ssDate)), 0, FirstSortedValue(Balance, ssDate)), L1, L2, L3, L4, Account))
its working. but need to check with DB. and come back to you sunny.
Awesome , I hope it is actually working
Best,
Sunny
You need an AGGR() when you nest a firstsorted() inside a sum(), and there´s a function Alt() that makes your logic (if null then "0")
sum(aggr(alt(FirstSortedValue([Balance], ssDate),0),Account) // supposing you need to aggregate by Account