Bar Chart

[krishna20]

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

[krishna20]

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.

[buzzy996]

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.

[sunny_talwar · Accepted solution]

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

[#5]

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

[krishna20]

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..

[sunny_talwar]

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

[krishna20]

Thank You Rahul for your suggestion.