Announcements
cancel
Showing results for
Did you mean:
Anonymous
Not applicable

## Average calculations from Excel files

Hi @all,

I'm new to Qlik Sense and thus I hope my question is still easily solvable.

I have various subcategories within my Excel file ordered by rows. It looks like this:

Group            Assessment

A                              1

A                              5

A                              5

B                              2

B                              2

B                              3

and so on.

I was wondering if and how I can calculate the mean and/or median of the categories A respectively B and if it's possible, can somebody recommend me a nice way to present it?

2 Solutions

Accepted Solutions
Partner - Specialist

Hi there

By using the Group as a dimension you can create a measure to show the mean or median. Below is an example in a table (with Group as Dimension and Avg(Assessment) and Median(Assessment) as measures):

You can play around with the visualisations to see which ones you like the best. You can convert a chart to another format  (if they can use the same data) by dragging the Chart from the left pane over the existing chart/table and selecting the Convert to option.

Below is an example of your data in a Combo chart using markers for the measures:

It is very easy to play around with visualisations in Qlik Sense so look for something you like☺. Please note that you can change the labels for the measures (I just kept them in so that you could see the formulas).

Regards,

Mauritz

Creator III
Not sure how much help you need exactly, but assuming you know how to load in your data, simply create a chart and add Group as dimension, and add Avg(Assessment) and/or Median(Assessment) as expressions. This is also assuming A and B are the only groups you have or you don't just want to group by A and B, but by all groups (if there are more than 2).

As for the best way to present this, I would personally prefer a simple table, but a bar chart could work too if you don't have too many groups.
2 Replies
Partner - Specialist

Hi there

By using the Group as a dimension you can create a measure to show the mean or median. Below is an example in a table (with Group as Dimension and Avg(Assessment) and Median(Assessment) as measures):

You can play around with the visualisations to see which ones you like the best. You can convert a chart to another format  (if they can use the same data) by dragging the Chart from the left pane over the existing chart/table and selecting the Convert to option.

Below is an example of your data in a Combo chart using markers for the measures:

It is very easy to play around with visualisations in Qlik Sense so look for something you like☺. Please note that you can change the labels for the measures (I just kept them in so that you could see the formulas).

Regards,

Mauritz

Creator III
Not sure how much help you need exactly, but assuming you know how to load in your data, simply create a chart and add Group as dimension, and add Avg(Assessment) and/or Median(Assessment) as expressions. This is also assuming A and B are the only groups you have or you don't just want to group by A and B, but by all groups (if there are more than 2).

As for the best way to present this, I would personally prefer a simple table, but a bar chart could work too if you don't have too many groups.