If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Dimensional reporting complex logic help needed

Started by cognos05, 15 Jun 2017 04:46:56 PM

Previous topic - Next topic

cognos05

# Accounts Purchasing               

                   All 5   4 of 5   3 of 5   2 of 5   1 of 5   0 of 5
Agency1         0      8                7               7            10            11
Agency2         7      9               31      47            47            29
Agency3         4      7               27      50            44            42

Hi basically this is a cross tab which would show agency in rows and respective columns in measure .

Agency Hierarchy -> Agency -> Rep->Accounts

Now there are five products A,B,C,D, E in products dimension .

All we need to do is count the number of accounts in Agency 1 which has sales in  only one of the products out of the 5 .
All we need to do is count the number of accounts in Agency 1 which has sales in  only two of the products out of the 5 .
All we need to do is count the number of accounts in Agency 1 which has sales in  only three of the products out of the 5 .
All we need to do is count the number of accounts in Agency 1 which has sales in  only four of the products out of the 5 .

I know how to do for ALL of 5 and 0 of 5 , I can use And condition on all products to check for sales >o , if all five are true then count else not .

for 1 of 5 , how would i know which one product has sales ?

I can think of using (Tuple(customer,P1,Sales)>0) but there are many product combinations here .
I know there should be way to achieve this .

Can any one help me with expression for 1of 5 calculation

Thanks,
Nithya