Expresion error

[Chanty4u]

Hi all,

Sunny T

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

[sunny_talwar]

Is the problem with Opening or Closing Balance? You are sharing the opening balance expression and pointing out that Closing balance is showing negative?

[Chanty4u]

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.

[sunny_talwar]

I see what you want, but I am not sure what might be missing. Is posting a sample a possibility bro?

[Chanty4u]

No bro. that is the issue

[sunny_talwar]

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

[Chanty4u]

yes sunny.. now its clear check below image

[sunny_talwar]

How about this:

Sum(Aggr(If(IsNull(FirstSortedValue(Balance, ssDate)), 0, FirstSortedValue(Balance, ssDate)), L1, L2, L3, L4, Account))

[Chanty4u]

its working. but need to check with DB. and come back to you sunny.

[sunny_talwar]

Awesome , I hope it is actually working

Best,

Sunny

[Clever_Anjos]

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