Text only | Text with Images

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: bpothier on 21 Mar 2013 10:30:05 AM

Title: Count Distinct
Post by: bpothier on 21 Mar 2013 10:30:05 AM
Hi There,

The report should return a 1 in the metric column if the criteria is met and a 0 if it is not.

The criteria are:
1 - Product = Jello
2 - Code = J001

If 1 and 2 are met then count the distinct number of order numbers and put a 1 in the metric field

I have attached what is happening and what i woudl like to happen

Driving me mad

Thanks

Bret       
Title: Re: Count Distinct
Post by: RKMI on 21 Mar 2013 11:20:25 AM
Hi,

Can you display the expression you have for the Metric col?

Thanks,
RK
Title: Re: Count Distinct
Post by: bpothier on 21 Mar 2013 12:22:32 PM
here it is

if((([Presentation Layer].[Revenue].[Product] Not Like '%EFI%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'EXP260%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'OF MCL ACC%')
and ([Presentation Layer].[Revenue].[Product] Not Like '%EX')
and ([Presentation Layer].[Revenue].[Product] Not Like '%FPPS')
and ([Presentation Layer].[Revenue].[Product] Not Like '%XEROX COLOR'))
and (([Presentation Layer].[Revenue].[XNAC Code] like 'AGJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ANJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'FZI%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIB%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'STP%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'UPS%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIX%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ADP%')))
then (count(distinct(substring([Presentation Layer].[Revenue].[Order Number],1,6))))
else (0)

Thanks for taking a look
Title: Re: Count Distinct
Post by: RKMI on 21 Mar 2013 12:43:14 PM
Hi,

Can you try this,

count(distinct(if((([Presentation Layer].[Revenue].[Product] Not Like '%EFI%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'EXP260%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'OF MCL ACC%')
and ([Presentation Layer].[Revenue].[Product] Not Like '%EX')
and ([Presentation Layer].[Revenue].[Product] Not Like '%FPPS')
and ([Presentation Layer].[Revenue].[Product] Not Like '%XEROX COLOR'))
and (([Presentation Layer].[Revenue].[XNAC Code] like 'AGJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ANJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'FZI%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIB%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'STP%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'UPS%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIX%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ADP%')))
then ((1) for [Presentation Layer].[Revenue].[Order Number])
else (0)))

Thanks,
RK
Title: Re: Count Distinct
Post by: bpothier on 21 Mar 2013 12:49:15 PM
I get an error

QE-DEF-0459 CCLException
QE-DEF-0260 Parsing Error before or near position 916

Title: Re: Count Distinct
Post by: RKMI on 21 Mar 2013 01:33:08 PM
Sorry my bad I put the for clause in the wrong bracket, try this please...

count(distinct(if((([Presentation Layer].[Revenue].[Product] Not Like '%EFI%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'EXP260%')
and ([Presentation Layer].[Revenue].[Product] Not Like 'OF MCL ACC%')
and ([Presentation Layer].[Revenue].[Product] Not Like '%EX')
and ([Presentation Layer].[Revenue].[Product] Not Like '%FPPS')
and ([Presentation Layer].[Revenue].[Product] Not Like '%XEROX COLOR'))
and (([Presentation Layer].[Revenue].[XNAC Code] like 'AGJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ANJ%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'FZI%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIB%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'STP%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'UPS%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'MIX%')
or ([Presentation Layer].[Revenue].[XNAC Code] like 'ADP%')))
then ((1) )
else (0))for [Presentation Layer].[Revenue].[Order Number])

Thanks,
RK
Title: Re: Count Distinct
Post by: bpothier on 21 Mar 2013 01:52:32 PM
hmmm

it is now returning all 1s in the metric column

shoudl be like                    is returning
2                                           1     
8                                           1
5                                           1
3                                           1                                       
Title: Re: Count Distinct
Post by: bpothier on 21 Mar 2013 02:00:30 PM
I attached the output to provide more detail
Title: Re: Count Distinct
Post by: bpothier on 25 Mar 2013 08:18:22 AM
could the running-count function work? I am having no luck getting it validated but I am thingkin something like

if ( Running-Count ( distinct(substring([Presentation Layer].[Revenue].[Order Number],1,6))) for Product, XNAC Code ) = 1 ) then ( 1 ) else ( 0 )

but I am pretty sure this is not the right format and also I am not sure how to tie in the limitations I need of product not like and nnac code like from the abiove statement
Title: Re: Count Distinct
Post by: RKMI on 25 Mar 2013 11:26:06 AM
Yeah, I'm also stumped I have tried number of different ways to get to your answer but no solution yet, at this point it feels like you might need to do this in two different data items where one picks up the case logic and other to do a count for each occurance... Anyways just talking out load hope that sparks an idea... I will keep working at it... In case you fig it out earlier let me know.

Thanks,
Rk
Title: Re: Count Distinct
Post by: bpothier on 25 Mar 2013 02:15:44 PM
thanks - it is driving me up the wall
Title: Re: Count Distinct
Post by: bpothier on 26 Mar 2013 12:57:31 PM
Here is what I did to get around it

I moved the Product and XNAC criteria to the detailed filter section and then just did a count distinct on order number in the data item.

Seems to be working as expected

Thanks for looking into it
Text only | Text with Images