Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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!

1 Solution

Accepted Solutions
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

View solution in original post

6 Replies
ogster1974
Partner - Master II
Partner - Master II

‌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

Not applicable
Author

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

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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!

Not applicable
Author

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;

Not applicable
Author

Yes this worked great!  Thanks so much!  One thing I am still trying to figure out though is how to show the labels inside the bar separated out on each color based off what the answer is.  Would you be able to help with this?

Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

Hi Amanda,

I don't think it's possible to place labels inside the bars, sorry!

Oleg Troyansky