Count Distinct

[bpothier]

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       

[RKMI]

Hi,

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

Thanks,
RK

[bpothier]

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

[RKMI]

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

[bpothier]

I get an error

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

[RKMI]

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

[bpothier]

hmmm

it is now returning all 1s in the metric column

shoudl be like                    is returning
2                                           1     
8                                           1
5                                           1
3                                           1                                       

[bpothier]

I attached the output to provide more detail

[bpothier]

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

[RKMI]

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

[bpothier]

thanks - it is driving me up the wall

[bpothier]

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