topic Re: Modified bar chart in QlikView

Modified bar chart

Tue, 08 Dec 2015 07:07:52 GMT

I have two tables

Table A

ID, AccountNo, FinancialYear, BudgetTotal, Commitments

1, 1, 2015, 6000, 500

Table B

ID, FinancialYear, FinancialMonth, ActualExpenditure

1, 2105, Jan, 450

1, 2105, Feb, 550

1, 2105, Mar, 400

1, 2105, Apr, 450

1, 2105, May, 550

1, 2105, Jun, 450

In table A we have one record per year per account, Table B keeps expenses per month.

Now I need to Create a bar chart with the first being a stacked bar chart with sum(ActualExpenditure) from Table B and sum(Commitments) from tableA. Then in a second bar I need sum(BudgetTotal)

The bar chart then would show 2850 as ActualExpenditure and 500 as Commitments in the stacked chart, and the 6000 as BudgetTotal in a second bar

Any suggestions on how to get this.

Re: Modified bar chart

nagarjuna_kotha — Tue, 08 Dec 2015 07:37:16 GMT

Hi,

PFA and let me know in case of any queries

-Nagarjun.

Re: Modified bar chart

Tue, 08 Dec 2015 08:05:11 GMT

I have seen something similar in a previous question. My inforrnation though is on two different tables The information on the bar chart is on two separate tables than I cannot load into Qlikview as one to be able to use the dimension field idea

Re: Modified bar chart

Peter_Cammaert — Tue, 08 Dec 2015 08:10:57 GMT

In the chart of the above example document (nice work btw nagarjuna.kothamandi), disable Show Legend and Label for Dim_2 (in tab Dimensions).

If you don't want to create an extra field in your script just to show two bars side-by-side, you can use the ValueList() function in a calculated dimension instead of Dim_2. ValueList() will create a synthetic dimension on the spot.

For example, replace Dim_2 with this expression:

=ValueList('Stack', 'Group')

And in your expressions, replace the test for a particular Dim_2 value with:

=IF(ValueList('Stack', 'Group') = 'Stack', Sum(Expr_N))

=IF(ValueList('Stack', 'Group') = 'Group', Sum(Expr_N))

Best,

Peter

Re: Modified bar chart

Peter_Cammaert — Tue, 08 Dec 2015 08:13:11 GMT

Doesn't matter. As long as you have a link between the two tables, this will work.

For an alternative to the second (artificial) dimension, see my post below.