9 Replies Latest reply: Oct 18, 2011 10:02 PM by gkgoh517 RSS

    How to include a 'Total' bar in a bar chart ?

      Hi,

      My source is a datamodel with few dimensions and fact table. I am trying to make a bar chart which shows the count of new joinees [Expression: Sum(New_Joinee_Count)]..based on the skill they pocess.. The requirement has 'Total' count to be shown as a seperate bar in the begining. I couldn't find any option for this. Can anyone help me with this. I'll provide some snap shots which would help you to know my situation better.

      error loading image

       

      error loading image

      error loading image

        • How to include a 'Total' bar in a bar chart ?
          Deepak Kurup

          hi,

          Getting total with 2 dimesnion is diffcult. may be another work around would be for you above sol will be adding two expression for years. like

           

           

           

           

           





           

          Sum

           

           

          ({<Year={$(=Max(Year))}>} Sale

          )

          and



           

          Sum

           

           

          ({<Year={$(=Max(Year)-1)}>} Sales)

          And also do enable the Total check boxx (Presentation tab)



            • How to include a 'Total' bar in a bar chart ?

               

              Hi Deepak,

              Suppose am ok with a stack way as shown...then will this makes things easier to get the 'total' bar in the begining?

              http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/4760.Stacked.bmp

                • How to include a 'Total' bar in a bar chart ?
                  Deepak Kurup

                  hi,

                  Adding two dimension and getting will be again tough in this case. Also it depends how you want to Analyse your data.

                  If you use stack then you will not able to compare to two yrs.

                  try two expression method it will work fine.

                    • How to include a 'Total' bar in a bar chart ?
                      John Witherspoon

                      Hmmm, yeah, it looks like the "Show Total" option isn't doing anything for a bar chart with two dimensions.

                      So you can handle it yourself with data by generating a table like this:

                      TypeGroup, Type
                      Total, Mainframe
                      Total, DotNet
                      Total, Java
                      Mainframe, Mainframe
                      DotNet, DotNet
                      Java, Java

                      Once that table exists, you just use TypeGroup as your dimension instead of Type. The data model will handle the rest. See the attached example for an efficient (if complicated) way to generate this table, and a working chart. If you just have a few known types like this, though, a simple inline load would be fine.

                      • How to include a 'Total' bar in a bar chart ?

                        Hi Deepak,

                        When I tried by giving this in the calculation field.. am not getting a valid chart.

                         

                        Sum({<[YEAR_NUM]={$(=Max(YEAR_NUM))}>} NEW_JOINEE_COUNT) and Sum({<[YEAR_NUM]={$(=Max(YEAR_NUM)-1)}>} NEW_JOINEE_COUNT)



                        But I noticed that when I have this below expression, it gives the right graph for latest year selection

                        Sum({<[YEAR_NUM]={$(=Max(YEAR_NUM))}>} NEW_JOINEE_COUNT)

                        and when I have this below expression

                        Sum({<[YEAR_NUM]={$(=Max(YEAR_NUM)-1)}>} NEW_JOINEE_COUNT)

                        seperately, I have the graph for the second lastest year. But getting a combination of both is failing.

                        Can you please help ?

                         

                          • How to include a 'Total' bar in a bar chart ?

                            Hi John,

                             

                            Thanks a lot for the helping hand. The example u posted look great. But as I am a beginner, I am not getting to understand it fully.

                            In the script side, what I do is just to SELECT all dimension tables and fact tables. I never use LOAD and am confused on seeing this. In my scenario, we have one dimension table which contain the skills which when joines to the fact table (fact table has a flag new_joinee_count) helps me to get the count of new joinees from a particular skill..

                            So..if you could explain it with respect to this scenario...it'd be a great help.

                              • How to include a 'Total' bar in a bar chart ?
                                John Witherspoon

                                When you do this:

                                SELECT Field1, Field2
                                FROM MyTable
                                WHERE whatever;

                                QlikView is letting you get away with a shortcut syntax. A more full syntax might look like this:

                                LOAD *;
                                SQL
                                SELECT Field1, Field2
                                FROM MyTable
                                WHERE whatever;

                                Basically, there's an implied load of all fields whenever you do a select in QlikView. This form of the load is called a "preceeding load". What it does is load its data from the results of the very next statement, in this case the SQL SELECT.

                                Now, that has very little to do with the example and how it works, but I wanted to point out that in a sense, you're already using LOAD statements; you just haven't been writing them explicitly.

                                Let me start by just explaining how my example works, and then you can see if you can figure out how to apply it to your own data. If you're still having problems, then we can look at your specific data next.

                                This part...

                                Data:
                                LOAD * INLINE [
                                Type, Year, Value
                                ...
                                ];

                                Is called an "inline load". When we write examples, we typically use inline loads instead of selects because we don't have access to your data and you don't have access to our data. An inline load can be reloaded by anyone, so makes for good examples. The idea is that the inline load is standing in for your SELECT statment. In your actual application, you probably have something that looks more like this:

                                Data:
                                SELECT Type, Year, Value
                                FROM SomeTable
                                WHERE whatever;

                                These two bits of script are equivalent, at least in the sense that they'll each load up a table called "Data" with fields Type, Year and Value. One just grabs the data from the text in the script, while the other reads some database using SQL.

                                There are a LOT of things that you can load from, though, and an inline table and an SQL SELECT are just two of them. The very next LOAD demonstrates another possible source:

                                Types:
                                LOAD fieldvalue('Type',iterno()) as Type
                                AUTOGENERATE 1
                                WHILE len(fieldvalue('Type',iterno()))
                                ;

                                The source here is AUTOGENERATE. It says to autogenerate only 1 row, but there's a WHILE loop here as well. So it will create rows as long as the while condition is true. The while condition is a bit complicted. First, let's talk about the fieldvalue() function. Internally, QlikView keeps a table of the distinct values of every field, I'm fairly certain in the order in which they were loaded. So even though you can't directly reference it as a table, somewhere internal to QlikView there's a data structure that looks something like this:

                                Type:
                                Row, Value
                                1, Mainframe
                                2, DotNet
                                3, Java

                                The fieldvalue() function takes advantage of this structure. So if I ask for fieldvalue('Type',2), it should return 'DotNet'. But instead of asking for a specific row, I'm asking for the row at iterno(). Iterno() is a fuction that returns where we are in the while loop. So the while loop will generate an iterno() = 1 on the first pass, then 2, then 3, and so on. So we are, then, looping through this internal table of the values of the Type field. Now, the while loop ends when the condition is no longer true. The condition is len(...). I want the length of the value returned by the fieldvalue() function. Internally, zero is false, and any non-zero value is true. So as long as I'm getting values back, this will be true. Once there are no more values, the len() will return 0, which is interpreted as false, and the loop ends.

                                So this load is essentially taking QlikView's internal table of the values of Type and making it accessible by turning it into a real table. We'll end up with this actual table:

                                Types:
                                Type
                                Mainframe
                                DotNet
                                Java

                                Now, why did we bother doing this? Couldn't we have gotten the same results with this simpler and clearer load?

                                Types:
                                LOAD DISTINCT Type
                                RESIDENT Data;

                                Yes, that would give us the same results. But it has to load from a table that has, in the example, six rows instead of from one that only has three rows. That doesn't matter much. But out in the real world, data tables might have tens or hundreds of millions of rows, but only a handful of values on those rows that we care about. It's then MUCH more efficient to load in those values from only a handful of rows than from tens or hundreds of millions of rows. So that's why I went to the additional effort, even though it doesn't matter for the example.

                                The next load demonstrates yet another source for data, a RESIDENT table:

                                Totals:
                                LOAD
                                'Total' as TypeGroup
                                ,Type
                                RESIDENT Types
                                ;

                                We use the RESIDENT keyword to tell it we're loading from a table that we've already loaded, that it already has in memory. In this case, we've already built the Types table. Now we want to take advantage of it to build yet another table. The result of this load will be this, and hopefully it's clear why:

                                Totals:
                                TypeGroup, Type
                                Total, Mainframe
                                Total, DotNet
                                Total, Java

                                Then we do another resident load, but this time, we tell it to CONCATENATE the data onto the Totals table. Concatenating means to just add everything new as new rows without trying to match up any values. It's like doing a UNION ALL in SQL. So the result after doing the concatenate will be this:

                                Totals:
                                TypeGroup, Type
                                Total, Mainframe
                                Total, DotNet
                                Total, Java
                                Mainframe, Mainframe
                                DotNet, DotNet
                                Java, Java

                                And that's the table I suggested generating. So the script generates the table I suggested.

                                Now, how does this table solve our problem? Well, we have four TypeGroups, Total, Mainframe, DotNet and Java. The Total TypeGroup connects directly to ALL of the Type values. So when doing a sum for the Total TypeGroup, QlikView will be summing the data for ALL of the Types. All of the other TypeGroups only map to a single Type. Therefore, all of the other TypeGroups will behave as if we were just looking at that Type, and will sum the data only for that type.