topic Re: Sum Values Having Max Date in QlikView

Sum Values Having Max Date

Tue, 08 Jan 2013 21:48:02 GMT

With this data:

Date, Name, Value

1/1/12, Bob, 3

1/1/12, Bob, 4

3/1/10, Bob, 5

2/1/12, Jane, 1

1/1/10, Jane, 5

I only need to return the sum of the values for each name, for only the max date for each name. So in this scenario, the result would be:

1/1/12, Bob, 7

2/1/12, Jane, 2

Can someone please let me know what the max, sum, aggr formula (or any formula) for computing the correct sum's?

Thanks very much!

Re: Sum Values Having Max Date

Tue, 08 Jan 2013 21:51:55 GMT

With Name as a dimention, try:

sum({$<Date={'$(=max(Date))'}>} Value)

Regards!

Re: Sum Values Having Max Date

Tue, 08 Jan 2013 21:59:50 GMT

Thanks, thats the right idea, but this solution only returns the max of all the dates:

2/1/12, Jane, 2

in this case.

I need the max of the dates per each person.

Re: Sum Values Having Max Date

Tue, 08 Jan 2013 22:12:12 GMT

Hi,

You can try using a Pivot table with the expression

sum({$<Date={'$(=max(Date))'}>} Value)

and Name as the dimension in the table.

Hope that helps.

Regards,

-Khaled.

Re: Sum Values Having Max Date

Tue, 08 Jan 2013 22:16:38 GMT

Thanks. I am using a Straight Table, although a Pivot Table has the same issue. Using this formla will return the max of ALL the dates. I need to aggr the dates by the Name so I can sum up the Value for each name using only the max date for each Name.

It's close but not quite there.

Re: Sum Values Having Max Date

nagaiank — Tue, 08 Jan 2013 22:55:35 GMT

Try the following which gives the result desired by you.

Temp:

load * Inline [

Date, Name, Value

1/1/12, Bob, 3

1/1/12, Bob, 4

3/1/10, Bob, 5

2/1/12, Jane, 1

1/1/10, Jane, 5

];

Temp2:

NoConcatenate

load Date, Name, Sum(Value) as Value2 Resident Temp group by Date,Name;

Drop Table Temp;

Data:

LOAD Name

, FirstSortedValue(Date,-Date) as MaxDate

, FirstSortedValue(Value2,-Date) as MaxValue

Resident Temp2 Group By Name;

Drop Table Temp2;

Re: Sum Values Having Max Date

Wed, 09 Jan 2013 14:28:59 GMT

If reloading is not an option, what expression can I use without reloading?

Thanks.

Re: Sum Values Having Max Date

flipside — Wed, 09 Jan 2013 15:34:21 GMT

Does this work? Can probably be amended to set analysis if you prefer.

=sum(Value*if(Date=aggr(nodistinct max(Date), Name),1,0))

flipside

Re: Sum Values Having Max Date

Wed, 09 Jan 2013 15:40:26 GMT

DING DING DING DING!!! Thank you, perfect! You win.

(Please let me know if you are open to questions offline that you can bill your time for.)

Thanks much,

Richard

Re: Sum Values Having Max Date

flipside — Wed, 09 Jan 2013 15:55:35 GMT

Cheers Richard, just test it thoroughly because (to be honest) I kind of stumbled on the NODISTINCT keyword solution for this one.

For small problems like this, there's plenty of help on here for free! I don't do the whole invoicing malarky at the moment, anyway.

flipside

Re: Sum Values Having Max Date

Mon, 02 Dec 2013 21:32:55 GMT

very nice!

Re: Sum Values Having Max Date

vaibhavb — Wed, 11 Mar 2020 11:58:42 GMT

Hi,

I used same as you explained, I am facing same problem and solved up-to certain level but for Drill-Down it works for the First Level only, When i am drilling down to next level or on-wards won't work.

sum(DAYSCLOSINGBALANCE*if(BALANCEDATE=aggr(nodistinct max(BALANCEDATE), FiscalYear),1,0))

Here is FiscalYear is DrillDown

FiscalYear -> Fiscal Month -> BalanceDate