Pass value from an Island table to chart expression

[#1]

Hi!

I have a lookup table which I don't want to join with any of source table due to changing requirements. It contains values that match (not in full) calculated dimensions in some charts. I need to create expressions in those charts that use values from a lookup corresponding to values in dimension(s). Could you please help with this?

Example:

Lookup:

AGE_18GROUP	Country	POPULATION	STANDARD_CODE
0 - 4 years	US	107258	150
0 - 4 years	US	113320	160
0 - 4 years	US	18986520	204
5 - 9 years	US	87591	150
5 - 9 years	US	104235	160
5 - 9 years	US	19919840	204
10 - 14 years	US	73785	150
10 - 14 years	US	93538	160
10 - 14 years	US	20056779	204
15 - 19 years	US	70450	150
15 - 19 years	US	73717	160
15 - 19 years	US	19819518	204

Chart (aggregated data):

AGE_18GROUP	Count of IDs	POPULATION	Ratio
0 - 4 years	100
5 - 9 years	200
10 - 14 years	300
15 - 19 years	400

How to get Lookup.POPULATION values corresponding to  Chart.AGE_18GROUP and Lookup.STANDARD_CODE=204 for Chart.POPULATION and use it further to get Ratio (Count of IDs/POPULATION)?

I really appreciate your help!

Thank you!

[MK_QSL]

Can you give what could be the result as per your requirement?

[#3]

It should be like this:

AGE_18GROUP	Count of IDs	POPULATION	Ratio
0 - 4 years	100	18986520	0.000005
5 - 9 years	200	19919840	0.000010
10 - 14 years	300	20056779	0.000015
15 - 19 years	400	19819518	0.000020

[MK_QSL]

Try This....

COUNT(ID)/SUM({<STANDARD_CODE = {204}>}POPULATION)

[#5]

Manish,

It doesn't work.

[#6]

Hi Elena,

Couple of questions: are the tables linked on AGE_18GROUP or is it really an island table, i.e. no link with the rest of the data set? What is the ratio a quotient of?

If possible, please post a sample as well.

Best,

Matt

[#7]

Hi Matt,

Yes, tables are islands.

Please see attached. I need to write a correct expressions for TOTAL and RATIO in a chart 'RESULT'.

Data is just for illustration purposes. Important thing - we should avoid join or mapping in script load. Picking up appropriate population value should be done on a report level.

Thank you!

Elena

[#8]

Hi,

This should work:

sum(if(AGE_18GROUP = AGE_GROUP AND STANDARD_CODE = 204, POPULATION))

See attached. My figures aren't matching exactly what you have above though since there are multiple ID fields per standard code. Any way we can limit the expression based on the ID field to get what you want?

Matt 

[#9]

Matt,

That formula produces wrong result.

I found the solution. I have to use AGGR on both fields AGE_GROUP and AGE_18GROUP while calculating maximum of POPULATION per corresponding age group and standard code =>

=aggr(max(if(AGE_GROUP=AGE_18GROUP and STANDARD_CODE = '204',POPULATION)), AGE_GROUP, AGE_18GROUP)