topic Re: Expresion error in QlikView

Expresion error

Chanty4u — Wed, 25 Nov 2020 16:16:04 GMT

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))

FirstSortedValue([Balance], ssDate)

)

Can anyone advice with correct syntax

Thanks

Chanty

Re: Expresion error

Gysbert_Wassenaar — Fri, 12 Aug 2016 06:21:49 GMT

=rangesum(FirstSortedValue([Balance], ssDate),0)

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 06:26:19 GMT

thanks gwassenaar. but it is also not working chk below image

as closing balance should not be in Negative . and opening balance is showing "0" only

Re: Expresion error

Gysbert_Wassenaar — Fri, 12 Aug 2016 07:06:31 GMT

How To Ask Questions The Smart Way

Re: Expresion error

Anil_Babu_Samineni — Fri, 12 Aug 2016 07:33:34 GMT

chanty,

I didn't find your calculation value. Can you post full expression. Sum(sales) similar that where is your SUM calculation field.

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 07:37:38 GMT

Hi anil,

this is for Closing balance : working fine

(

isnull(FirstSortedValue([Balance], TBDate))

sum(0) + Sum (Debit) - Sum (Credit)

FirstSortedValue([Balance], ssDate) + Sum (Debit) - Sum (Credit)

)

this is for openg bal :Not working

sum(if (

isnull(FirstSortedValue([Balance], ssDate))

FirstSortedValue([Balance], ssDate)

)

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 09:41:09 GMT

Not sure, but did you try Gysbert's solution with a DISTINCT?

=RangeSum(FirstSortedValue(DISTINCT [Balance], ssDate),0)

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 09:46:40 GMT

it is givng error sunny not allowing to use Distinct. chk below image for your ref

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 09:49:24 GMT

Expression Editor doesn't understand the syntax, try clicking ok and see if the result comes out as desired. This was an issue in the older version of QlikView.

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 09:59:23 GMT

yes sunny even though result is wrong it self.

it is not caluclating the sum and the closing balance is showing negative.

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 10:01:35 GMT

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

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 10:07:12 GMT

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.

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 10:09:48 GMT

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

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 10:10:35 GMT

No bro. that is the issue

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 10:12:37 GMT

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

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 10:19:58 GMT

yes sunny.. now its clear check below image

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 10:22:54 GMT

How about this:

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

Re: Expresion error

Chanty4u — Fri, 12 Aug 2016 10:28:46 GMT

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

Re: Expresion error

sunny_talwar — Fri, 12 Aug 2016 10:30:03 GMT

Awesome , I hope it is actually working

Best,

Sunny

Re: Expresion error

Clever_Anjos — Fri, 12 Aug 2016 11:36:39 GMT

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