Count data that is filtered out...

[Chris_]

Hi,

Firstly I think I should explain my report and then what I'm looking for - it may be more clear this way.  My report has five querys in it and acts as one of our HR Dashboards.

The report has one query calculation - employee age. (Years between current date and birth date)
First filter is to display employees where the age is greater than 58.
Second filter is to display employees with a null value as termination date. (Display active employees only)

The crosstab, has on the left hand side the operating unit and as the measurements has a query calculation that counts Employee Age.  This gives a report with Business Units down the left hand side, ages 59 - 74 at the top and a count/pivot style count of how many of each age in each business unit.

At the end is a total number of employees in that range at the right hand side.

I'd like to add a headcount in here, but as my filter for Age > 58 is in place it won't count employees outwith that range - is there a way around this?  I'd like to add the headcount query in, and put in a calculation to display a percentage of the workforce who's age is >58.

Any help appreciated.

[twlarsen]

How about creating a data item that counts employees over the age of 58 and another data item that counts all employees?

[Chris_]

I tried doing this, however the crosstab displayed all cells for all ages even if data didn't populate it which was a bit of a pain.

I laid it out like.

if (Age>58) then (count global ID) else (null)

Would this be correct?

[Lynn]

Try this:
- alter your Age calculated data item:
if ( years_between(current_date,birth_date) <= '58')
then ('Under 58')
else (years_between(current_date,birth_date))

- create a data item in the query that just has the number 1 in it. Set aggregation to total.

If the query includes the unique ID for each employee then Cognos will aggregate up the numbers based on what you included in your layout. You can now identify the total headcount as well as the count by any age for further calculations.

If you need to use the count funtion the syntax would be count(item for <scope>) rather than within an if-then-else as you've shown. something like count(globalID for operatingUnit, Age) seems closer to what you might want assuming the age query item is like I described above.

Hope this helps...

[Chris_]

Hi,

Thanks for your reply.

I got a little lost having a go at that - I'm still very much at a junior level using Cognos.

I did try something else though that I think might work if I can find a little workaround to fix the crosstab, I created the original "Age" Query Calc and put Age at the top of the Crosstab and Operating Unit down the side.

For the measures, I created a query calculation that went like

Count(
if (Age>58) then (1) else (null)
)

And that's worked fine... but along the top all ages are shown and all those under 58 have 0's.



Is there any way I could get the rows at the top to start only when there is data to count below it? Eg at 58?

Thanks,

Chris.

[Lynn]

There are options for zero suppression, but I'm not sure how your approach solves the original problem of getting the total headcount.

What I tried to explain before (perhaps poorly) was to create a data item that either has the age or a basic label "Under 58". Then you'd also have a data item with the number 1 in it that gets counted up. You'd also have the global ID (unique id for a person) and operating unit.

So you're query would have the global ID, OperatingUnit, Age, CountPeople. When you look at the tabular data you'd see something like:
1234   UnitA  61  1
1235   UnitA  Under 58  1
1236   UnitA  61   1
etc.

basically every person is returned in the query with an attribute that is either their age (if they are 58 or older) or a generic bucket label "Under 58" if they are younter than 58. Each row simply has the number 1 which is the measure.

Then in the crosstab you'd put operating unit as the rows and your age label as the columns and your countPeople measure as the measure. This should show a count for each operating unit and age but everyone under 58 gets lumped into that one category instead of broken out by actual age. Cognos would automatically aggregate all those 1s because globalID is not in the layout. In my silly data example you'd see UnitA with a count of 1 for the Under 58 column and a count of 2 for the age 61 column.

The total of all the columns would be the total headcount.

You can't mix datatypes in an expression, so you I think you'd need to cast the years between calculation to character so it matches the text data type of "Under 58" part of the expression.

[Chris_]

Thanks again for the explaination Lynn... I'm doing my best to try and understand and make this work.

At the moment the issue I am having is with the age calculation, when I put in the "Under 58" it constantly fails and only accepts validation if a number is entered - any reason why this is?  I am using Cognos 8.3.

Hopefully we are on the right tracks, my end result I'd like to look a bit like this...



The headcount figure we don't need to display, but it's required to work out the percentage based on the total/headcount.

[Lynn]

I don't quite understand what you mean about the age calculation failing, but you can't have an expression that returns a number in some cases and text in others. Maybe you are mixing data types and that is the problem.

So years_between returns a number which means, for example, that if you want a text label "Under 58" if the person is 52 and just the age if the person is 61, then you'd have to cast the calculated date to a string. I think the original rough expression I gave you didn't address this and apologies if that was the cause of confusion.

In the example below, the "if" portion compares a number to a number (result of years_between calculation and the number 58). You have to compare apples to apples. Similarly, the result of both the "then" and the "else" must be the same datatype. So the "then" portion is a string (the literal 'Under 58'). The "else" portion is also a string because the age calculation that normally returns a numeric is casted to a varchar.

if ( years_between(current_date,birth_date) <= 58)
then ('Under 58')
else (cast(years_between(current_date,birth_date),VARCHAR(10)))

If this isn't what the problem is then maybe you could provide the expression that is causing problems.

[Chris_]

Thanks Lynn,

Good progress again has been made, I think we are nearly there.

A screenshot below shows the current state of the report, I've verified the data is correct.

Is it possible to remove the "Under 58" column?  The measures the report is currently using is a simple count of Global ID.

[Lynn]

You could try using a conditional style to suppress display of the Under 58 information.

Select the Crosstab Node member for the columns (click on the age heading) and then create an advanced conditional style by clicking on that property. The expression would be [age] = 'Under 58' and then in the advanced tab for the conditional style, set the box type to none.

Next select the crosstab intersection and apply the existing conditional style to that.

[Chris_]

Hi Lynn,

This has worked well - thanks.  I have one small problem I didn't think about.

I got the table looking with the ages and the numbers below.  I then added in the total to the right hand side and it's totalling the headcount... is there a way to have it only total the ones in the table?  I can then do my % calculation based on the headcount total against the numbers in the pivot.

Thanks again, Chris.

[Lynn]

Hmmm. Not sure I have the best solution -- sometimes my ideas get convoluted, especially with cross tabs :)

You could create a data item that resolves to either 'Hidden' or 'Visible' depending on the value of your age label.

Delete the total you created earlier, then nest that new data item on top of the age. When you total with that arrangement you'll get a sub-total for visible (which would be 59, 60, etc.) and a separate sub-total for hidden (Under 58). Then apply a conditional style to hide the nested stuff you added.

You might do better to post this particular question in a new thread so people don't have to wade through everything before making suggestions. Bastically you have a crosstab with a particular column hidden using a conditional style. so the question is how to show the total for only the visible items and use the total of both hidden and visible as part of a percentage calculation...i bet someone else will come up with a better way for you.

[Chris_]

Thanks Lynn - I'll create a new thread and see if any suggestions are added.

Again, I really appreciate your help getting to this stage.

[Lynn]

Happy to help Chris. I learn a lot with Cognos by thinking about other people's challenges. Sometimes I find it easier to deal with requirements that come my way because I played around with something similar.