Count formula not counting Correctly

building reports webintelligence
1

Hi All-

I’m creating a report that shows how many journals we post in an accounting period. Of those journals we posted I am also showing how many lines are produced.

I went ahead and created a crosstab and brought over the variable Journal ID and Journal Line.

For did a count on the Journal ID which is the following:
=Count([Journal ID];Distinct)

I added distinct in the formula b/c I wanted to count the occurence of the same journal once.

The key issue I am having is the Journal Line.

I extracted the raw data in excel and created a pivot table. Within the pivot table I did a count on Journal ID and Journal Line. The Journal ID gives me the wrong number since it is counting the same journals multiple times. But the Journal Line gives me the correct counts.

I tried creating this BO report and I was able to get Journal ID counts to work, thanks to the formula I showed above, but the Journal Line count is not working properly.

This is the formula I am using for Journal Line count:

=Count([Journal Line])

This formula is literally counting each line occurrence once. For example:

Journal ID-Journal Line
305ABC - 1
248OYE - 6
PREPAY12 - 1
RENT72 - 8
BUY98 - 12
REV69 - 1

So in the example above the formula has a count of 4 (1, 6, 8, 12). In reality I would like it to say 6 (1, 6, 1, 8, 12, 1).

Is there anyway to work around this to make it count 6 instead of 4?

Thanks in advance,
Luis

lconstance :us: (BOB member since 2012-04-26)

2

If I understand you correctly you are looking for the count of the combined dimensions.
Try this :
Count ([Journal ID]+[Journal Line])

tbunnik :netherlands: (BOB member since 2005-06-08)

3

This works. Thanks!

lconstance :us: (BOB member since 2012-04-26)