topic Re: Date of aggr formula in QlikView

Date of aggr formula

h_demarco — Tue, 29 Nov 2016 19:29:09 GMT

Fellows,

I have a formula to return maximum value exposed in a customer:

Max(Aggr(Sum(If([Issue Date]<=Date,[Billed amount]))-Sum(If([Payment Date]<=Date,[Paid amount])),Date))

This sum all billed amount before a date and subtract all paid amount before the same date.

Aggr gives me an statement with balance (Billed amount - Paid amount) day after day (Date).

Columns are: Date, Billed amount, Paid amount, Balance

First objective was to get highest balance value. That is done.

Secondary is to get last Date when highest balance occurs (I am considering the possibility of having same balance for more than one row).

How to get this 'last Date'? I already tried Firstsortedvalue but not luck.

Thanks in advance.

Re: Date of aggr formula

MK_QSL — Tue, 29 Nov 2016 19:30:52 GMT

FirstSortedValue(Date,

-Aggr(Sum(If([Issue Date]<=Date,[Billed amount]))-Sum(If([Payment Date]<=Date,[Paid amount])),Date))

FirstSortedValue(Distinct Date,

-Aggr(Sum(If([Issue Date]<=Date,[Billed amount]))-Sum(If([Payment Date]<=Date,[Paid amount])),Date))

Re: Date of aggr formula

sunny_talwar — Tue, 29 Nov 2016 19:30:55 GMT

Would you be able to share your application or a sample with expected output?

Re: Date of aggr formula

MK_QSL — Tue, 29 Nov 2016 19:34:01 GMT

FirstSortedValue(Distinct Date,

-Aggr(Sum(If([Issue Date]<=Date,[Billed amount]))-Sum(If([Payment Date]<=Date,[Paid amount]))-Date/1E6,Date))

Re: Date of aggr formula

h_demarco — Tue, 29 Nov 2016 21:07:40 GMT

Second option worked.

Thanks for your fast reply.

Re: Date of aggr formula

swuehl — Tue, 29 Nov 2016 21:54:15 GMT

Just a note:

I don't think that

FirstSortedValue(Distinct Date,

-Aggr(Sum(If([Issue Date]<=Date,[Billed amount]))-Sum(If([Payment Date]<=Date,[Paid amount])),Date))

guarantees that the max date for the max balance is returned, it returns just any one date with the same sort weight, which one might be depending e.g. on the load order field Date.

If you want to make your expression more robust, follow e.g. Manish's last suggested solution (though I think the small value proportional to Date should be added, not subtracted, but could be wrong).