topic Re: Dimension limits - table in QlikView

Dimension limits - table

Anonymous — Fri, 22 May 2015 18:27:55 GMT

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

Re: Dimension limits - table

Anonymous — Fri, 29 May 2015 13:28:35 GMT

Anyone have any thoughts for help?

Thanks again,

Justin

Re: Dimension limits - table

Fri, 29 May 2015 13:38:28 GMT

in your expression for weight,

can u try using

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

i m not 100% sure. please try

Re: Dimension limits - table

Fri, 29 May 2015 13:56:25 GMT

may be after this u can use

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

Re: Dimension limits - table

Anonymous — Fri, 29 May 2015 13:58:42 GMT

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

Re: Dimension limits - table

Anonymous — Fri, 29 May 2015 14:40:46 GMT

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({<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]))