Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
m_fleischmann
New Contributor III

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?

 

Thanks in advance for upcoming answer 🙂

Tags (4)
2 Solutions

Accepted Solutions
Mauritz_SA
Contributor III

Re: Average calculations from Excel files

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):

 

Qlik Table.PNG

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:

 

Qlik_Sample_Graph.png

 

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
Contributor III

Re: Average calculations from Excel files

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
Mauritz_SA
Contributor III

Re: Average calculations from Excel files

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):

 

Qlik Table.PNG

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:

 

Qlik_Sample_Graph.png

 

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
Contributor III

Re: Average calculations from Excel files

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.