Discussion Board for collaboration related to QlikView App Development.
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!
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.
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)
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!
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.
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
please share the sample data and the required output format
Avg({<ID_Time = {"$(=Min(ID_Time))"}>} Num_Goals)
To much confidential data
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
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.