Average calculations from Excel files

[Anonymous]

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?

Thanks in advance for upcoming answer 🙂

[Mauritz_SA · Accepted solution]

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

[jensmunnichs · Accepted solution]

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.