6 Replies Latest reply: Jan 17, 2016 8:51 PM by Oleg Troyansky RSS

    Count

      Hello,

       

      I am trying to create a stacked bar chart that will show the total of the "Status" field, but then also break it out by the two results that are in that field "Calculated" and "Uncalculated".  I am new to Qlik Sense, and can't seem to figure out what I need to do here.  I cannot share the data as it is confidential, but was hoping that someone might be able to walk me through what I would do in this scenario.  I know I need the date in the dimensions, and then I am guessing probably two versions of the status in the measure, but trying to figure out how to get it where it counts the "Calculated" and counts the "Uncalculated".

       

      Thanks!

        • Re: Count
          Andy Weir

          i take it you have some factual data that the status field is a component part of.  What you want to do is count ofn the key of that table and Group by status then set the presentationlayover to a stacked bar.

          Set that up and qlik will do the rest

          • Re: Count
            hogan dan

            Let's say you have a table with three columns: Date, Calculated and Uncalculated.

             

            To create a Stacked Bar Chart where the Date values are displayed in the X-axis and the values of the Calculated and Uncalculated columns are stacked in the Y-axis:

            1. Create a new sheet

            2. Click on the Edit button in the toolbar

            3. Drag the Bar Chart icon from the Charts selector in the left column of the screen and drop it in the Workspace.

            4. Click on Add Dimension and select Date from the list.

            5. From the Data section in the Right Column of the screen, click Add Data then click Measure and select Calculated from the list and then select an aggregation such as Count, Sum, Average, whatever. If there is only one value in the table for each date you can use Sum or Average. Count will display the number of values in the Calculated column that match each of the Date values.

            6. Repeat Step 5 for the Uncalculated column.

            7. Click on Appearance in the right column of the screen and select Stacked and set Value Labels ON.

            The sum of the values of Calculated and Uncalculated will appear near the top of each stacked bar. When you mouse-over each bar the values of each of the Measures(Calculated, Uncalculated) is displayed.  I believe that you can't make the display of individual measure values remain displayed without the mouse-over action.

            Good luck

            • Re: Count
              Oleg Troyansky

              Hi Amanda,

               

              based on your description, you have a field Status with two possible values "Calculated" and "Uncalculated". You want to create a stacked bar chart by Date, with the total counts of the two statuses.

               

              You can approach it in two ways:

               

              - 2 Dimensions, 1 Measure, or

              - 1 Dimension and 2 Measures.

               

              I think it's easier for you to use the former - 2 Dimensions. So, add Date and Status as the two dimensions. In the measure, use the formula COUNT(Status) 

               

              This should do the job for you.

               

              The other way would be to use Date as a Dimension, and calculate individual Measures for each one of the Status values. For that, you could use Set Analysis, but that gets a bit more technical...

               

              cheers,

              Oleg Troyansky

              Upgrade your Qlik skills at the Masters Summit for Qlik, now with the new and redesigned curriculum!

              • Re: Count
                hogan dan

                Amanda,

                How do you differentiate between Status values that are "calculated" and "uncalculated"?

                Does the Status field have just one or the other, or can it have both?

                 

                In the LOAD script, you can use logic that will set values for Calculated and Uncalculated.

                Example: (assuming the status value is either calculated or uncalculated)

                LOAD

                if(Status=whatever_value_indicates calculated,Status,'') AS [Calculated],   // creates a Dimension named Calculated and sets the value to that of column Status if it is determined to be "calculated" else it sets it to ''.

                if(Status=whatever_value_indicates uncalculated,Status,'') AS [Uncalculated],  // creates a Dimension named Uncalculated and sets the value to that of column Status if it is determined to be "uncalculated" else it sets it to ''.

                DATE;