topic Re: Complex aggr query in QlikView

Complex aggr query

Thu, 05 Jul 2012 08:33:17 GMT

Hi Everyone,

I have following table

ID Date Amount

1 01/07/2012 50

1 02/07/2012 12

1 02/07/2012 13

1 05/07/2012 14

2 01/07/2012 22

2 04/07/2012 23

3 01/07/2012 32

3 03/07/2012 12

what i want is sum of amont for the max id , whose max date is less than 1 day from current date

i.e. first id 1,2 should be picked on the basis of date and then sum of amount (22+23) from max of the id's (from 1 & 2 , 2 should be picked )

Anonymous — Thu, 05 Jul 2012 08:39:33 GMT

Why should ID=1 be picked? It's max date is 05/07/2012 (i.e. not less than 1 day from current date)

Thu, 05 Jul 2012 08:42:16 GMT

ok, 1 or less day i.e. current day and the previous day .

for today and yesterday id's should be picked (1,2) and from them 2 should be picked since 2 is greater than 1 .

Thu, 05 Jul 2012 08:43:54 GMT

Hi,

Can you also provide an output table based on your input? I don't quite get what you want.

Regards,

Xue Bin

Thu, 05 Jul 2012 08:45:50 GMT

output should be in a textbox with value 45 (i.e. 22+23)

Anonymous — Thu, 05 Jul 2012 08:46:11 GMT

So you're saying that you want to sum up the amount for the max(ID) whose maximum date is not equal to today or the previous day?

Thu, 05 Jul 2012 08:49:26 GMT

i mean max(date) should be today or yesterday ,, and from them max id

sorry for bad explanation

Anonymous — Thu, 05 Jul 2012 08:57:08 GMT

Ok, I'm thinking that there's a much easier way to do this but here goes:

=sum({$<ID={$(=max({$<Date={'$(=date(Today(),'DD/MM/YYYY'))','$(=Date(Today()-1,'DD/MM/YYYY'))'}>} ID))}>}Amount)