topic Re: Bar Chart in QlikView

Bar Chart

krishna20 — Tue, 31 Mar 2015 11:58:56 GMT

Hi Friends,

I'm having two fields TP_Year and BD_Year. The two years are from different tables. Actuals is the amount from policy table based on TP_Year and Budget is the amount from budget table. I need to show Sum(Actual) and Sum (Budget) in a single bar chart.

Here the years are different. Even though policy and budget table linked together,the year fields are different. I need to compare the both the amounts in a single chart.How it could be done? ! Please anyone suggest me.

Regards

Krishna

Re: Bar Chart

sunny_talwar — Tue, 31 Mar 2015 12:04:22 GMT

You can disregard both TP_Year and BD_Year and use ValueList() function to create years for your specific chart and then then use if statements to create your bar chart. The exact dynamics may have to looked into further, but I have used this approach lately.

HTH

Best,

Sunny

Re: Bar Chart

PrashantSangle — Tue, 31 Mar 2015 12:44:21 GMT

Hi,

Concate both table try like

load TP_Year as Year,

Actuals

from Policy_Table;

Concatenate

load BD_Year as Year,

Budget

from Budget_Table;

Regards

Re: Bar Chart

krishna20 — Tue, 31 Mar 2015 13:21:30 GMT

Hi Max,

In Bud_Year there is only two years.In TP_year there is years from 1995 to 2015.

Is we will get expected output by concatenating two tables?

Re: Bar Chart

PrashantSangle — Tue, 31 Mar 2015 13:24:41 GMT

Hi,

Try it, If not work then let me know.

Regards

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 07:09:39 GMT

Hi Max,

This Approach Doesn't works.

Please give me any other suggestion.

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 07:25:13 GMT

Hi,

Can you please give me an example using valuelist() how to do it.

Re: Bar Chart

Wed, 01 Apr 2015 09:52:18 GMT

do you have sample data that you can share?

Depending on your data model and the base year you want to use you could join the field into the parent table.

Regards

Rahul

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 10:17:00 GMT

Hi,

As much as data reduced and attaching sample file. Pls suggest on me this.

Re: Bar Chart

sunny_talwar — Wed, 01 Apr 2015 10:48:37 GMT

Please find attached the example using value list as well as using a inline table.

Although tedious it can give you what you seek.

HTH

Best,

Sunny

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 11:37:24 GMT

Hi Sunny,

Thanks for your response. I tried valuelist technique as you suggested. Budget values are repeating further. Can you please check in my sample app.

Re: Bar Chart

buzzy996 — Wed, 01 Apr 2015 12:15:00 GMT

by using script first try to show ur data in one table like below,that helps to show the same data in one bar chart.

Year,Tp_year,Budget_Year,Actual amount, budget amount

2013, 2013, 2013 ,1,738,212,190 , 20,001,705,062,00

2014, 2014, 2014 ,1,679,703,821 , 27,435,313,818

etc

are u tried dreamer4 suggestion?,u have try the same manner,tht's the solution I believe.

Re: Bar Chart

sunny_talwar — Wed, 01 Apr 2015 12:21:58 GMT

Does the data loaded in the application look right to you?

I see for one date there are more than 10 Budget_Amount. If this is not incorrect, then your Budget_Amount for 2013 is going very high forcing your other bars to not show up.

PFA the app and the budgeted data.

Best,

Sunny

Re: Bar Chart

Wed, 01 Apr 2015 12:23:14 GMT

Hi Krishna,

Looks like you have a budget amt against each Month, TP_COMP_CODE, TP_DIVN_CODE and TP_LOB_CODE. If that is correct you need to rework your data model.

If i was you i would load the Budget table first:

Budget:

LOAD BUDGET_MONTH,

COMP_CODE as TP_COMP_CODE,

DIVN_CODE as TP_DIVN_CODE,

LOB_CODE as TP_LOB_CODE,

BUDGET_YEAR ,

Data as BUDGET_AMOUNT,

MONTH_NO,

BUDGET_DATE,

MonthName(BUDGET_DATE) as Policy_Month,

BUDGET_DATE as TP_APPR_DT

FROM

(qvd);

Then i would load the Policy table and join the budget info back to it like following:

Policy:

Load

TP_PREM_LC_1-TP_DISC_LC_1+TP_LOAD_LC_1 as TP_GrossPremium_LC_1;

LOAD

TP_COMP_CODE,

TP_LOB_CODE,

TP_DIVN_CODE,

TP_DEPT_CODE,

TP_PROD_CODE,

TP_POLICY_NO,

TP_DISC_LC_1,

TP_LOAD_LC_1,

TP_APPR_DT,

Monthname(TP_APPR_DT) as Policy_Month,

Year(TP_APPR_DT) as TP_Year

FROM

(qvd);

left join

Load

Policy_Month,

TP_COMP_CODE,

TP_DIVN_CODE,

TP_LOB_CODE,

BUDGET_AMOUNT

Resident Budget;

drop table Budget;

Note i have created a new field Policy_Month which is one of the field i am joining the two tables on and then i am dropping the budget table.

On the front end you can then use TP_Year as your dimension and for expression you would have use sum(TP_GrossPremium_LC_1) for actual and Avg(BUDGET_AMOUNT) for Budget amount.

I believe in theory this should work, i could not test as i do not have the source data.

Regards

Rahul

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 14:13:44 GMT

Hi Sunny and Max,

Thank you very much. I combined your both ideas and got expected one. Sunny Is there any way not by hard coding years.?If any please give me a suggestion..

Re: Bar Chart

sunny_talwar — Wed, 01 Apr 2015 14:28:19 GMT

You can definitely automate things, you just need to use variables to do that. For instance you want to see a chart which start from the Max(TP_Year) and goes back 3 years (so 2015, 2014, 2013, 2012). You can use variable to extract maxYear from the TP_Year and then use Where statement to create a new Year table (instead of inline or using ValueList) and then use variables in the front end of the application to give you most recent data.

So in summary automation is possible, you just need to know how many years worth of data you plan on seeing.

HTH

Best,

Sunny

Re: Bar Chart

krishna20 — Wed, 01 Apr 2015 17:04:04 GMT

Thank You Rahul for your suggestion.