topic Re: Set Analysis in QlikView

Set Analysis

alec1982 — Wed, 04 Jun 2014 19:55:40 GMT

Hi guys,

I need to do Sum amount of the most recent date. The below formula sums everything in the past. Is there away to fix to show only the most recent period amount?

=Sum({$<[Date]={"<$(=today())"},[Type]={'AR'}>}[Amount])

Re: Set Analysis

maxgro — Wed, 04 Jun 2014 19:58:08 GMT

what do you mean for

the most recent period

Re: Set Analysis

jolivares — Wed, 04 Jun 2014 19:58:37 GMT

Change to :

=Sum({$<[Date]={">=$(=today())"},[Type]={'AR'}>}[Amount])

Your expression sum all amount before today, changing the bracket you sum from today to....

Re: Set Analysis

alec1982 — Wed, 04 Jun 2014 20:02:00 GMT

I want to sum amounts paid in the past or today but only one line(Most Recent).

Date Amount

05/01/2014 100

06/01/2014 250

06/07/2014 500

07/01/2014 220

in this case the result should be 250

Re: Set Analysis

alec1982 — Wed, 04 Jun 2014 20:03:44 GMT

here is the formula to calculate the desired date.. but not sure how to get the amount

date(max({$<[Payment Date]={"<=$(=today())"}>}[Payment Date]))

Re: Set Analysis

alec1982 — Wed, 04 Jun 2014 20:26:38 GMT

most recent means the last Amount scheduled today or prior to today's date.

Re: Set Analysis

maxgro — Wed, 04 Jun 2014 20:31:55 GMT

maybe

=FirstSortedValue({$<Date={"<$(=today())"}>} Amount, -Date)

=subfield(concat(aggr(sum({$<Date={"<$(=today())"}>} Amount),Date), '-', -Date), '-',1)