If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Using a generic date in the Crosstab

Started by FirstStruck, 20 Jul 2011 04:00:40 PM

Previous topic - Next topic

FirstStruck

Hello!

I am trying to calculate turnover in a crosstab.
This report will calculate turnover for each month in a year. It needs 2 data fields to do this:
# employees paid during period
# employees termed

Then a query calculation will divide terms/paid*12 to give the annualized turnover percentage for each month.

The way the information is pulled, I can't figure out how to crosstab both data items. Currently I am using 2 different crosstabs -one for terms, one for # paid. If they are on 2 different crosstabs I can't make the query calculation to calculate the turnover percentage.

Down the left of the crosstab is each restaurant and accross the top is Year and then under year Month. The problem is how the query calculation for the Year & Month fields are written.

The # employees paid field counts the number of unique names paid from all checks combined that have a paydate within the month. The Year query calc says Year(Paydate) and the month says Month(Paydate).

The # employees termed field is based off termination dates. This filter says termination dates between 2010-01-01 and 2010-12-31. The Year query calc says Year(Term Date) and the month says Month(Term date).

How can I get them to talk to each other? I want to make it understand that terms in the Month(term date) need to line up next the # paid in the Month(Paydate). Is there a way to populate Year/Month without specificying they are the Year or Month of a data field?

I hope I've described this clearly enough for you all. Please let me know if you need any additional details and thank you so much for helping!