question about the set analysis

[#1]

Hi ,

I have a below table, and I would like to get the [amount] of the agency on the agency_contract_date, the expressions are not work when I use set analysis and IF for the calculation. please kindly refer to the dimention below and the attached qvw. could you please kindly advise? thanks a lot!

dimention:

=aggr(

Maxstring(if(agency_contract_date>=date('20120101','YYYYMMDD')

and agency_contract_date<date('20130101','YYYYMMDD'),agency))

,agent)

expresssion 1:(not work)

sum({$<transaction_date={$(max(agency_contract_date))}>}

amount)

expresssion 2:(not work)

SUM(if(transaction_date=max(agency_contract_date),

amount))

agency	agent	leader	agency_contract_date	transaction_date	amount
1001	8001	0	20110101	20120301	140
1001	8001	0	20110101	20120401	150
1001	7001	0	20110101	20120301	140
1001	7001	0	20110101	20120401	150
1008	8001	1	20120501	20120501	160
1008	8001	1	20120501	20120601	170
1008	8001	1	20120501	20120701	180
1002	8002	0	19991101	20120201	170
1002	8002	0	19991101	20120301	180
1002	8002	0	19991101	20120401	190
1009	8002	1	20120501	20120501	200
1009	8002	1	20120501	20120601	210
1009	8002	1	20120501	20120701	220
1010	8003	1	20120601	20120601	800

[Gysbert_Wassenaar]

See attacthed qvw. Is that what you need? If not explain in more detail. Tell us what the result table should be.

[#3]

Thanks a lot Wassenaar, you result is almost near what I want, but can't fix the issue that i am facing.

Could you please advise why the below expression is not work using the below dimension?

Seems the "max(agency_contract_date)" is not the maxinum agency_contract_date of the agency but the maxinum agency_contract_date of the whole table.

dimension:

=aggr(

Maxstring(if(agency_contract_date>=date('20120101','YYYYMMDD')

and agency_contract_date<date('20130101','YYYYMMDD'),agency))

,agent)

expresssion 1:(not work)

sum({$<transaction_date={$(max(agency_contract_date))}>}

amount)

[Sokkorn]

Hi,

Your expression 1 should be like this

Sum({$<transaction_date={$(=Date(max(agency_contract_date),'YYYYMMDD'))}>} amount)

Regards,

Sokkorn

[#5]

Thanks Sokkorn, Please refer to my reply above.