topic Re: Set Analysis Confusion in QlikView

Set Analysis Confusion

Anonymous — Mon, 18 Jul 2016 11:40:27 GMT

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

Re: Set Analysis Confusion

rubenmarin — Mon, 18 Jul 2016 12:27:19 GMT

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))

Re: Set Analysis Confusion

Anonymous — Mon, 18 Jul 2016 13:46:18 GMT

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.