Chart

[Beez]

Hello all,

New bee to qliksense, need to plot a pie chart  for male : female ratio on every year or on specific duration. Say for the year 2023 how many male: Female ration Form this data:

Would you please help me. thanks

[stevedark]

Hi @Beez 

You will need to use the WHILE statement to iterate to get a row for each employee for each year they appear in.

Something like:

LOAD
   1 as RowCount,
   *,
   Year(AddMonths([Starting Date], 12*(IterNo()-1))) as Year
   WHILE AddMonths([Starting Date], 12*(IterNo()-1)) < YearEnd([Ending Date])
   ;
LOAD
   [Employee Name],
   Gender,
   [Starting Date],
   [Ending Date]
FROM [data source] (type);

This assumes that the data source has the dates as numerics (e.g. Excel or a database), if not you will also need to use a Date# function to convert the dates so that you can do math on them.

You will then get two rows for John, three for Jane etc., each with the Year stamped on the rows.

You can then just do sum(RowCount) to get the sum of rows, with a dimension of Gender and put that in a pie chart. To get a chart per year you can either hard-code with set analysis, e.g. sum({<Year*={2023}>}RowCount) or use a trellis to get a chart per year.

Hope that points you in the right direction.

Cheers,
Steve

https://www.quickintelligence.co.uk/blog/

[Beez]

@stevedark Thank you for your kind help. Let us imagine if there is a sub section like this. For some employee ID the end contract is not defined. I want to calculate the active employee for the year 2021. The results here needs to be 5. The employee iD 5 and 7 needs to be excluded.

I wrote the expression as such:

LOAD
1 as RowCount,
*,
Year(AddMonths([Début de contrat], 12*(IterNo()-1))) as Year
WHILE AddMonths([Début de contrat], 12*(IterNo()-1)) < YearEnd(IF(IsNull([Fin de contrat]), Today(), [Fin de contrat]));

But there is an error. Would you please suggest whats the best. Excuse me for the bombarding message.

thanks

[stevedark · Accepted solution]

Hi @Beez 

That logic seems sound, you could simplify the if statement by using a rangemin statement:

< YearEnd(RangeMin([Fin de contrat], today()))

What error message are you getting? Can you share a screenshot.

It may be prudent to put a backstop on the iterations, by adding something like:

and IterNo() <= 100

I doubt anyone is going to stick around that long in any job!

It could be that the end dates are not null, but are empty strings instead?

When I sent the code, I typed it directly in the message without testing in Sense. Make sure I didn't miss pairing a bracket or anything like that.

Excluding employees can be done in the WHERE statement.

Steve