Dimension limits - table

[Anonymous]

Hello,

I am looking for some help properly coding a straight table to show the bottom 5 based off of a weight.  I have included a picture of the entire table on the left which is only showing 4.  If I uncheck the suppress missing or suppress 0 value, the table brings in a state that has null for both.  I would like to also show the variance even if one of the years presents a null value.  Not sure if there is a way to turn null into a 0.  Any thoughts on a way to use the rank function, or another way to show the bottom 5 regardless of selections made?

Thank you,

Justin

[Anonymous]

Anyone have any thoughts for help?

Thanks again,

Justin

[#3]

in your expression for weight,

can u try using

if isnull(expression,'0',expression).

i m not 100% sure. please try

[#4]

may be after this u can use

=AVG(AGGR(rank($<risk_state=>expression)),risk state))

[Anonymous]

Hi,

I tried the first option, and it fixed some of my top 5 tables, but not all of them.  I will try the second option now.  Thank you so far for the help.

Justin

[Anonymous]

Not sure if the second option was working properly, I was getting a syntax error on the dollar sign expression.  The table then seemed to sort only on alphabetical.  Here is the syntax I used:

=AVG(AGGR(rank($<[RISK STATE]=>(Sum ({<[CAT IND]={'N'}, YEAR={$(vBusinessYear)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[DIRECT EARNED PREMIUM])

/Sum ({< [CAT IND]={'N'}, YEAR={$(vBusinessYear)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[EARNED EXPOSURES])-

Sum ({<[CAT IND]={'N'}, YEAR={$(vBusinessYearPrior)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[DIRECT EARNED PREMIUM])

/Sum ({< [CAT IND]={'N'}, YEAR={$(vBusinessYearPrior)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[EARNED EXPOSURES]))/

(Sum ({<[CAT IND]={'N'}, YEAR={$(vBusinessYearPrior)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[DIRECT EARNED PREMIUM])

/Sum ({< [CAT IND]={'N'}, YEAR={$(vBusinessYearPrior)}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[EARNED EXPOSURES]))

*(Sum({<YEAR={'PLAN'}, [NEW RENEW IND]={'COMBINED'}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>}[DIRECT EARNED PREMIUM])

/Sum(total {<YEAR={'PLAN'}, [NEW RENEW IND]={'COMBINED'}, [MONTH-NUMERIC] = {"<=$(=max({<YEAR={$(=max(YEAR))}>} [MONTH-NUMERIC]))"}, [LINE OF BUSINESS]={'OTHER'}, [DATA ORIGIN]={'SMG'}, [MONTH-TEXT]=>} [DIRECT EARNED PREMIUM])))),[RISK STATE]))