topic Re: Including a sum statement into Firstsorted value in QlikView

Including a sum statement into Firstsorted value

Mon, 10 Dec 2012 14:45:34 GMT

Hello I have a problem. I am getting a null value because there are two records in my table with the same date, which is fine, but Qlikview displays this as a null, I have used expression FirstSortedValue([Cleared Amount],-Date) I would like to use FirstSortedValue(SUM[Cleared Amount]),-Date) but Qlikview doesnt like it, I could do a resident load but the report takes over an hour to reload.

Is is it possible to use sum in a firstsortedvalue statement?

Regards.

Paul.

Re: Including a sum statement into Firstsorted value

jonathandienst — Mon, 10 Dec 2012 14:55:28 GMT

Perhaps this:

FirstSortedValue(Aggr(Sum([Cleared Amount]), Date), -Date)

Hope that helps

Jonathan

Re: Including a sum statement into Firstsorted value

swuehl — Mon, 10 Dec 2012 14:57:42 GMT

You can't embed an aggregation function (sum()) into another (FirstSortedValue() ) without using advanced aggregation, so try something like

=FirstSortedValue( aggr(sum([Cleared Amount]),Date), -Date)

Or if you don't want to aggregate your Amounts because they are just duplicates, try DISTINCT qualifier:

=FirstSortedValue(DISTINCT [Cleared Amount], -Date)

Re: Including a sum statement into Firstsorted value

Mon, 10 Dec 2012 15:44:11 GMT

Unfortunately that didn't work, Still got a null value,

Paul.

Re: Including a sum statement into Firstsorted value

swuehl — Mon, 10 Dec 2012 15:48:43 GMT

Are you trying to use the expression in script or in a chart expression?

Re: Including a sum statement into Firstsorted value

Mon, 10 Dec 2012 15:50:47 GMT

Normal expression Swuehl, I am trying to sum to value amounts (off different rows) that have the same date.

Paul.

Re: Including a sum statement into Firstsorted value

sidjustice — Thu, 08 Aug 2013 17:00:23 GMT

Did you ever find an answer to this issue? I am having the same issue and would appreciate if you could share your solution