Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
Can you give what could be the result as per your requirement?
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 |
Try This....
COUNT(ID)/SUM({<STANDARD_CODE = {204}>}POPULATION)
Manish,
It doesn't work.
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
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
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
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)