Skip to main content
Marching toward a simplified navigation! READ ON
Showing results for 
Search instead for 
Did you mean: 
Contributor III
Contributor III

Set Analysis within Set Analysis

I have an expression.


I want to replace the set analysis with "Select all the document number where Hierachy Level 1 is Buisness".

The reason I am doing this is because there are values in  Hierarcy level 2  which dont come under Hierarchy Level 1 "Buisness". But these Hierarchy level 2 values have Document numbers associated with them that come under "Business". when someone selects a Hierarchy level that dose not associate with  Hierarchy Level 1 "Buisness" will show data for all the Document number common Between   Hierarchy level 2  selection and the ones that come under  Hierarchy Level 1 "Buisness".

I have written the Following set analysis foe the same but it dosent seem to work.

=COunt({$<[Document Number]=P({$<[Hierarchy Level 1]={'Procurement'}>}[Document Number])>}distinct[Document Number])

can anyone please help. Its very urgent.

1 Solution

Accepted Solutions

Try this


=Count({$<[Document Number] = P({$<[Hierarchy Level 1] = {'Business'}, [Hierarchy Level 2]>}[Document Number])>} DISTINCT [Document Number])


View solution in original post

2 Replies
Contributor III
Contributor III

For better under standing of the problem I have tried to simplify it below:


suppose field Hierarchy level 1 has a value 'Business'. Anothwer fiel 'document number' has values 1,2,3,4,5 associated with this hierarchy Level 1 ='Business ' . Now suppose some one selects a value from field Hierarchy Level 2 has a value 'X' and this 'X' dosent come under hierarchy Level 1 'Business '(It becomes grey when  hierarchy Level 1 ='Business ) . but This X has document number 2,3 associated with it. 


Now if some one selects this   Hierarchy Level 2 'X' the data should be shown for  document number 2,3. 
But if I write this expression

=count({<Hierarchy Level 1 ={'Business'}>}Document_number)

this expression will give '0' as output as Hierarchy Level 2 'X' dosent come under Business. So instead I am trying to bring the document number associated with the Hierarchy level 1 Business. Something like this.

=Count({$<[Document Number]=P({$<[Hierarchy Level 1]={'Business'}>}[Document Number])>}distinct[Document Number]).

I am expecting this set analysis to give me THe Docuemnt numbers associated with Business.So it becomes something like this:

=Count({<Document Number = {1,2,3,4,5}>}Document Number). If this works and Some one selects Hierarchy Level 2 'X'. The data should be shown as Hierarchy Level 2 'X'' as Document number 1,2 associated with it.

but its not working


Try this


=Count({$<[Document Number] = P({$<[Hierarchy Level 1] = {'Business'}, [Hierarchy Level 2]>}[Document Number])>} DISTINCT [Document Number])