Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Skip the ticket, Chat with Qlik Support instead for instant assistance.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Bar Chart - Average based on min values

Hi experts,

I've the following data model:

    Dim_Team:
          -ID_Team
          - Coach

    Dim_Player:
          - ID_Player
          - Name
          - Position
     Dim_Time:
          - ID_Time (0...90)
          - Game_Shift (1_Half and 2_Half)
     Dim_Calendar:
          - Date_ID
          -Year
          -Month
          -Day
   Fact_Game:
         - ID_Game
          - ID_Player
          - ID_Time
          - ID_Team
          -ID_Date
          - Num_Goals

My goal: On average , how many first goals occur by ID_Time

What I've is:

IF(ID_Time = Min(ID_Time), Count(Num_Goals))

But I isn't correct because it doens't give me the average. I'm trying to use this on bar chart.

Thanks!
         

1 Solution

Accepted Solutions
swuehl
MVP
MVP

What about the percentage I suggested in my previous answer:

=Count(Distinct ID_Game) / Count(TOTAL DISTINCT ID_Game)

This will normalize the absolute numbers by dividing by the number of games. So you can compare e.g. different seasons or teams better.

Or maybe I am missing what you are after. Could you post a small sample distiribution and what you expect as 'average'?

You can use mock-up data, so no need to post confidential data. Just data to clarify your setting and where you're heading to.

View solution in original post

9 Replies
swuehl
MVP
MVP

Maybe like this:

Create a histogram for first goals:

1) Create a calculated dimension like

=Aggr( Min(ID_Time), ID_Game)

2) Then create an expression like

=Count(Distinct ID_Game)

or if you want to get the percentage:

=Count(Distinct ID_Game) / Count(TOTAL DISTINCT ID_Game)

Not applicable
Author

Thanks for your answer

With that I get the total by ID_Time, right? How can get the average?

Maybe create a variable with the count then in expression use:

AVG(variable).  ?

Thanks!

tresesco
MVP
MVP

Not sure if I understood right. May be, like:


Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)


if not, try to share a representative sample.


Not applicable
Author

The question that I want to answer is (imagine):

At the Time "20" (ID_Time), in average, how many times occur the first goal of the match

avinashelite

please share the sample data and the required output format

Anonymous
Not applicable
Author

Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)

Not applicable
Author

To much confidential data  

Not applicable
Author

Imagine that the chart have the hour in dimension:

1: In average how many 1ªfirst goals occur in the ID_Time = 1
2: In average how many 1ªfirst goals occur in the ID_Time = 2
3: In average how many 1ªfirst goals occur in the ID_Time = 3
4: In average how many 1ªfirst goals occur in the ID_Time = 4
5 In average how many 1ªfirst goals occur in the ID_Time = 5


To know when is a 1ªfirst goal, I have a timestamp column to identify the minimum...

I think your expression is correct -

Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)


However, with that the chart only return the first time value

swuehl
MVP
MVP

What about the percentage I suggested in my previous answer:

=Count(Distinct ID_Game) / Count(TOTAL DISTINCT ID_Game)

This will normalize the absolute numbers by dividing by the number of games. So you can compare e.g. different seasons or teams better.

Or maybe I am missing what you are after. Could you post a small sample distiribution and what you expect as 'average'?

You can use mock-up data, so no need to post confidential data. Just data to clarify your setting and where you're heading to.