topic Re: Bar Chart - Average based on min values in QlikView

Bar Chart - Average based on min values

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Bar Chart - Average based on min values

swuehl — Mon, 07 Mar 2016 23:14:04 GMT

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)

Re: Bar Chart - Average based on min values

Tue, 08 Mar 2016 07:19:07 GMT

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!

Re: Bar Chart - Average based on min values

tresB — Tue, 08 Mar 2016 07:28:45 GMT

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.

Re: Bar Chart - Average based on min values

Tue, 08 Mar 2016 09:16:21 GMT

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

Re: Bar Chart - Average based on min values

avinashelite — Tue, 08 Mar 2016 09:21:54 GMT

please share the sample data and the required output format

Re: Bar Chart - Average based on min values

Anonymous — Tue, 08 Mar 2016 09:26:21 GMT

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

Re: Bar Chart - Average based on min values

Tue, 08 Mar 2016 09:46:15 GMT

To much confidential data

Re: Bar Chart - Average based on min values

Tue, 08 Mar 2016 09:51:52 GMT

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

Re: Bar Chart - Average based on min values

swuehl — Tue, 08 Mar 2016 10:41:21 GMT

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.