Sum on last date within group and second condition

Tue, 28 Oct 2014 15:11:22 GMT

The simple phrase is "Total of Sales from records when the last action on a account resulted in a status of "complete"

I'm working in textboxes and I'd like to get a total based on two conditions,

1) The record has the max(time) within a group (just that record, not the sum of the entire group)

2) A field in that record equals some text

Record#	Account	Update DateTime	Status	Sales
1	123456	1/1/14 1 pm	Assigned	1
2	123456	1/2/14 2 pm	Complete	2
3	987654	1/2/14 1 pm	Complete	4
4	987654	1/2/14 2 pm	Complete	8
5	987654	1/3/14 1 pm	Complete	16
6	456987	1/2/14 1 pm	Complete	32
7	456987	1/3/14 2 pm	Assigned	64

Here are two examples and my expected output

1) No filters, Total for "Complete" is 18, "Assigned" is 64

- Records 2, 5, and 7 are considered because they are the last time within their respective account. One text box will sum up Complete (2+16), the other Assigned (64)

2) The only date selected is 1/2/14, Total for "Complete" is 42, "Assigned" is 0

- The filter for date (1/2/14) removes all but 2, 3, 4, and 6; The filter for last account times within those leaves 2, 4 and 6. (Since I want only one record per account, I take the record with the max time [that falls within the date filter] in account 987654) One text box will sum up Complete (2+8+32), the other Assigned (0)

I've been trying all sort of things with FirstSelectedValue(), Aggr(), set expressions and some if() statements.

=Sum(

Aggr(

FirstSortedValue({$<

[Status]={'Complete'}

[Sales],

-[Update DateTime]

)

,[Account]

)

The problem with this is filtering on status gives strange results.

Re: Sum on last date within group and second condition

MK_QSL — Tue, 28 Oct 2014 15:45:05 GMT

Can you please elaborate little more with another example or explain bit more on the example you have given how you want 20, 64 44 and 0 results...

Re: Sum on last date within group and second condition

Tue, 28 Oct 2014 16:14:25 GMT

Added some explanation in blue text, also changed the table a little to have a record# and broader base.

topic Re: Sum on last date within group and second condition in QlikView

Sum on last date within group and second condition

Re: Sum on last date within group and second condition

Re: Sum on last date within group and second condition