4 Replies Latest reply: Dec 8, 2015 3:13 AM by Peter Cammaert RSS

    Modified bar chart

    Andre Toerien

      Hi

       

      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 k

          Hi,

           

          PFA and let me know in case of any queries

          -Nagarjun.

            • Re: Modified bar chart
              Andre Toerien

              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

                In the chart of the above example document (nice work btw nagarjuna), 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))

                 

                or

                 

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

                 

                Best,

                 

                Peter