Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Not applicable

Distribution chart

Hello,

I like to add a chart with a distribution (scatter or histogram): X rounded meeting time (read categories) Y count of employees with that average meeting length. What I already have in a chart: the rounded average meeting length with employee as dimension. See underneath.

Knipsel.PNG.png

Formula for the average meeting length (the "if" statement has to do with time registrered not related to any meeting, which would pollute the average meeting time).

=ROUND((SUM(IF(Meeting.Meetings>0,Meeting.Time))+SUM(IF(Meeting.Meetings>0,Meeting.BreakTime))+SUM(IF(Meeting.Meetings>0,Meeting.Other)))/SUM(Meeting.Meetings),30)

(1 records has the number of meetings incl. time, breaktime and other time per employee per interval > 1 employee can relate to many records)

What I would like is to present: the numbers as a distribution like the underneath one.

Knipsel2.PNG.png

(don't like to say this, but the chart above was made in Excel in a couple of seconds > individual averages exported to Excel > pivot table on the length > chart with X distinct lengths, Y count)

What I have trouble with: getting the distinct rounded meeting lengths on the X-axes and also the count of the rounded averages (trouble there is that first the average has to be calculated per employee, after which a count has to be made of those averages).

Has anyone a suggestion how I could realise this (frontside only if possible)? Many thanks for any thoughts.


3 Replies
bbi_mba_76
Valued Contributor

Re: Distribution chart

Hi,

maybe you could use the formula for the average as calculated dimension and then an expression having the count.

Re: Distribution chart

Perhaps a calculated dimension class(rangesum(Meeting.Meetings,Meeting.BreakTime,Meeting.Other),30) and count(Meeting.Meetings) as expression. Post sample data if you need more help.


talk is cheap, supply exceeds demand
Not applicable

Re: Distribution chart

Hi, the class function did help, thanks for the suggestion.

Next step is getting the per employee dimension integrated which can than be counted. Class now results in a single class (with the overall average, since the employees are not taken into account). Basically 1 bar in the graph. I'll have a look if I can create some sample data (actual data is >100K records with ~200K meetings).

A few sample records  (not actual records, but same problem)

INPUT records
EmplMeetingTimeMeetingBreakMeetingOtherMeetingMeetings
120050302
125040401
140030301
260075603
220050302
230030301
380050301
340060402
310050301
345090303
430050302
520050302
525040401
540030301

The chart underneath is the result I'm aiming for.

Knipsel3.PNG.png

Community Browser