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

Crosstab without end, amen

Started by winndm, 04 Nov 2010 03:35:06 PM

Previous topic - Next topic

winndm

Greetings,
I produced a Crosstab report in Impromptu a few years ago which has filled our needs, but now that we are using Cognos8, I want to replicate it.
It seems my Impromptu report was using local processing to calculate some cells, and I can't do that in my C8 environment (and I don't want to).

I think the direction I'm supposed to be going is joins or unions, but I don't understand these concepts very well.

This is simpler when I show you some sample data.  At each month end, I capture a YTD widgit count for all customers.  Since customers come and go, I have lots of null cells.


Customer     JAN     FEB     MAR      APR
1                22       23       25        NULL
2               NULL    NULL    12         14
3                20       40      NULL     NULL



At the end of January, I know my YTD widgits was 42, just by totalling that column in the crosstab.  No problem.

February works too, 63, everything makes sense.

Now in March, customer #3 disappeared.  There is no data at all for this customer.  I need to grab the 'most recent record' and replace his null value. March should total 77, but since I have a null, I'm only showing 37.

I spent some time thinking about "how to grab just the most recent YTD number", but the problem in how I'm thinking about that is I can already see that I could potentially put "14" in customer #2's January and February records.

I suppose my "most recent YTD number" criteria isn't actually true.  Its "most recent AFTER customer first contact" sort of thing.

So I'm stumped, mostly out of ignorance.  I'm a quick learner tho, if anyone has a favorite way of thinking about this sort of problem.



Many thanks,
Dave Winn