Count Expression returning data from filtered rows

Question

Hi,

I have applied filters to a column Z where I have created formula to return a value of 1 to which I want to see these rows only. Anything without 1 will be filtered out and not visible. E.g. rows before and after my desired range of data.

Next, when I wish to return the count of how many times a description occurs by Reference Number then I would use the  expression along the lines of  count(ColumnX."Description" by ColumnX."Description", ColumnY."Reference")

Now, this expression does work however in the subject area I am working on there are some situations where it returns data that is filtered out, e.g. outside my desired range.

In other workarounds I have applied the msum logic so it's searching row by row and it has worked but not for this particular example.  I only have experience in Answers so I am unable to interfere with the settings behind the subject area.

Does anyone have any suggestions when I only want to carry out the count expression only for the desired range and not to pick up "hidden" rows of data?

Thanks,

Jas

Jaspal D · Answer

Hi Jerry,

Thanks for the response.

"It looks like you need to add your column Z to the BY clause of your COUNT function." - Unfortunately I tried this already where I tried to group by the relevant Column  =1 but this only brought back ones and zeroes throughout, e.g. whereas I had 2-5's before where the majority of these figures were correct.

"COUNT(CASE WHEN date BETWEEN X AND Y THEN ColumnX."Description"  ELSE cast(NULL as char) END by column Z, Column Y)"  - The range of data relates to a sequence of events, for example an audit trail where a Sequence Number is provided - I believe this sequence number that has been created is causing the issues for some reason! Again, it returns values of 1's.

There is nothing wrong with your logic, it is just for some reason one of the columns created in the database causes an issue for which I can't change.

If I was to add a filter where it removed the case when the Start Case Description  = the end case description then these do not get counted  - which is what I want and further demonstrates that the Sequence # column is somehow not liked by OBIEE

FURTHER INFO: In pivot table view the numbers look to be correct for cases when the description is before the lower range but is incorrectly counted when the description is above the upper range.......

In table view it counts both outside the lower and upper range

Jerry Casey · Answer

It looks like you need to add your column Z to the BY clause of your COUNT function.

Another option is to use a case logic to null-out records you don't want counted.

COUNT(CASE WHEN date BETWEEN X AND Y THEN ColumnX."Description"  ELSE cast(NULL as char) END by column Z, Column Y)