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