topic Re: question about the set analysis in QlikView

question about the set analysis

Thu, 11 Jul 2013 17:09:41 GMT

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

Re: question about the set analysis

Gysbert_Wassenaar — Thu, 11 Jul 2013 17:27:01 GMT

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

Re: question about the set analysis

Fri, 12 Jul 2013 07:03:57 GMT

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)

Re: question about the set analysis

Sokkorn — Fri, 12 Jul 2013 07:10:48 GMT

Hi,

Your expression 1 should be like this

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

Regards,

Sokkorn

Re: question about the set analysis

Fri, 12 Jul 2013 08:50:46 GMT

Thanks Sokkorn, Please refer to my reply above.