topic Re: Bar chart help in QlikView

Bar chart help

Anonymous — Thu, 13 Nov 2014 21:59:37 GMT

Hello community,

Looking for some help on a bar graph that I am having difficulty doing the formula for. Here is what I am looking to produce:

I am looking to graph ratios by state which I am able to do. I am also looking to sort the graph by a different expression which I have also done. I am having difficulty getting the formula correct and looking for an idea. Here is what I need:

The formula should involve the variance in ratio for 2014 from plan, while also taking into account the weight of the state. IE a variance in a bigger state is a big deal. I cannot figure out how to represent the relative nature of the state. Here is my formula so far:

((Sum ({<YEAR={2014}, MONTH={'6'}>}[Losses])/Sum ({<YEAR={2014}, MONTH={'6'}>}[EARNED PREMIUM])-
Sum ({<YEAR={'PLAN'}, MONTH={'6'}>}[Losses])/Sum ({<YEAR={'PLAN'}, MONTH={'6'}>}[EARNED PREMIUM])) * weight of state

I thought of doing (sum of loss / sum of earned premium) / (count of loss / count of earned premium) = weight of state. That did not seem to work.

Looking for some help on this. Here is a copy of the graph:

Re: Bar chart help

MK_QSL — Thu, 13 Nov 2014 22:20:44 GMT

In denominator, you need to use COUNT or SUM as per your given definition?

Re: Bar chart help

jduenyas — Fri, 14 Nov 2014 04:24:41 GMT

In your formula you have Year={'PLAN'}...

How can you have a string, for Year?

If PLAN is a variable then enclose it with parenthesis but it does not seem to be a variable.

I think you need to revisit your formula and correct the errors.

Re: Bar chart help

Anonymous — Fri, 14 Nov 2014 13:15:48 GMT

Hello Manish and Josh,

Plan is a year listed in our model, which is why we can use a string. There are no errors currently, outside of that I cannot visualize how to get the weight or the percentage of earned premium each state is. Ultimately I am just asking how to get the weight relative to the whole so I can multiply that against the variance. Any ideas?