5 Replies Latest reply: Nov 21, 2016 1:36 PM by Sarah Sullivan RSS

    Create Bar Chart with Multiple Dimensions

    Christopher Brown

      Can someone explain to me how I create the Bar chart in QlikSense below. I have a Dimension for (Goods, R&D and Services) and a Measure for Cost.

       

      How can I:

      a) Add a dimension for Total Cost

      b) Add multiple years to the chart

      c) Format the Y (Cost) axis to Billions

       

      Thanks

       

      Capture.PNG

        • Re: Create Bar Chart with Multiple Dimensions
          Sarah Sullivan

          If you want to have different types of Cost (Goods, R&D...) and the total on the same plot then you will likely need to create a summary table in your data model that contains at least 3 columns Year, Cost and Cost Type, where the cost type field contains possible values of Total, Goods, R&D, and Services).  If you want to be able to parse to other levels of detail it could contain more columns, like maybe Quarter or Month or Department.  Once you have the summary table, then the plot in QlikSense has two dimensions (Cost Type and Year) and 1 measure Cost.

           

          To get the Cost to show as Billions, you will need to divide the measure by 1 Billion and then adjust the number format or label

          .2016-11-17_14-23-27.png

            • Re: Create Bar Chart with Multiple Dimensions
              Shraddha Gajare

              You can try using dummy dimension in this case

              valuelist('Total Cost','Goods','R&D','Services')

               

              and then use 4 different measures for each FY.

               

              It might help

              • Re: Create Bar Chart with Multiple Dimensions
                Christopher Brown

                Thanks for the reply Sarah,

                 

                I have created a summary table by:

                Summary:

                LOAD * Inline [

                Year, Cost, CostType

                "Fiscal Year", "Action Obligation", "G-R&D-S"

                ];

                 

                How do I tie my data (from my other tables) to these parameters in my data model? Where can I place my SUM function?

                Thanks,

                Chris

                  • Re: Create Bar Chart with Multiple Dimensions
                    Sarah Sullivan

                    Chris - The method to create the summary table will depend on your data model.  If you data model has a transaction table that looks something like this right now will each cost category as its own field then to create the summary table you would use:

                    Transaction:

                    Transaction#DateFYGoodsR&DServices
                    zzz1/1/2016FY15$10$50
                    zzz21/1/2015FY140$1000
                    zzz31/1/2014FY1300$50
                    zzz41/1/2013FY12$250$10

                     

                    Summary:

                    Load

                         FY,

                         sum(Goods) as Cost,

                         'Goods' as CostType

                    Resident Transaction

                    Group by FY;

                     

                    You would repeat this for each type (Goods, R&D, Services,etc...).  This would link back to the rest of your data model by FY (or you could link by date if you prefer).

                     

                    If your data model looks more like this with all the costs in one field.

                    Transaction:

                    Transaction#DateFYCostsType
                    zzz1/1/2016FY15$10Goods
                    zzz1/1/2016FY15$5R&D
                    zzz21/1/2015FY14$100R&D
                    zzz31/1/2014FY3$50Services

                     

                    Summary:

                    Load    

                         FY,

                         Type as CostType,

                         sum(Costs) as Cost

                    Resident Transaction

                    Group by FY, CostType;

                     

                    You would not need to repeat and it would also link back to your data model by FY.

                     

                    For more on the group by and other load statements I would suggest this nice write up:

                    LOAD data into QlikView

                     

                    Hope this helps.

                • Re: Create Bar Chart with Multiple Dimensions
                  Rohit Kumar

                  HI Chris,

                   

                  First, Create 4 measure with respect to FY12 ...FY15 with the help of set analysis. Drag 4 bar chart and use dimension which has value as ,'Goods','R&D','Services' .

                   

                  Note. By above statement, you have to make new chart for FY16 or I can say it will not a dynamic visualization. But for now it will solve your purpose.

                   

                  Please mark the appropriate replies as CORRECT / HELPFUL so our team and other members know that your question(s) has been answered to your satisfaction.

                   

                   

                  Rohit Kumar