Set Analysis Confusion

[Anonymous]

Hi All,

I'm hoping this is a simple problem that I have been looking at for too long.

I have the following expression

=(count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'}

,[PHBusinessUnit]-={' '}

,[PHSupplierName]={"=((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))>10)"}

,[PHSupplierName]={"=((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))<51)"}

>}PHDocumentNumber))

Each of the individual elements of this expression work, and if I include the the first three elements everything works fine, however when i add in the fourth element the expression fails.

What I am trying to achieve is to only show those Suppliers (the only dimension) that have between 11 and 50 unique documents within the last 180 days that have a valid business unit.

I've tried using '>10<51' in a single element but that doesn't seem to work either.

Thanks in advance for any help you can give.

Rob

[rubenmarin]

Hi Rob, maybe:

=(count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'}

,[PHBusinessUnit]-={' '}

,[PHSupplierName]={"=((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))>10 and count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))<51)"}

>}PHDocumentNumber))

If you use the same field two times, it only keeps the last one.

Another option:

=(count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'}

,[PHBusinessUnit]-={' '}

,[PHSupplierName]={"=((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))>10)"}

>*<[PHPIMInpDate]={'>=$(=Date(today()-180))'}

,[PHBusinessUnit]-={' '}

,[PHSupplierName]={"=((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))<51)"}

>}PHDocumentNumber))

[Anonymous]

Thanks Ruben,

This didn't quite work, but pointed me in the right direction, I found that each of the elements in the PHSupplierName needed to be in () and then the whole lot needed to be in () as well. So it looked like this

=(count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'}

,[PHBusinessUnit]-={' '}

,[PHSupplierName]={"=(((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))>10) and ((count(DISTINCT{<[PHPIMInpDate]={'>=$(=Date(today()-180))'},[PHBusinessUnit]-={' '}>}PHDocumentNumber))<51))"}

>}PHDocumentNumber))

Thanks for your help.