Count Olap

[cognos05]

HI Experts,

I have a Product group and it has children
One product group might have 4 products and one might have 3 products in it .

I need to do  a count for each customer what is the count of products that thay have sale above 10k for each product group
                           Product Group1             CountProductGroup1
                          p1      P2        P3
Customer A        13000   5000   12000            2

so i am expectign the result 2 as count . I dont know how to iterate each item under product group1 to check if each has greater than 10000 sales  and then count .

Here I have shown P1 P2 P3 , but it could be any number, so I have to come up with some dimensional function to get the sales of children item check and then sume the count .

Any suggestions is appreciated.

Thanks,

[cognos05]

i was able to get this working .

count([H0 Sales] within set filter(children([ProductGroup]),tuple(currentMember([Sales Cube].[ShipToCustomer].[Customer]),[Rolling 12 Months],[Sales])>5000))

[MFGF]

i was able to get this working .

count([H0 Sales] within set filter(children([ProductGroup]),tuple(currentMember([Sales Cube].[ShipToCustomer].[Customer]),[Rolling 12 Months],[Sales])>5000))

That seems like a good solution, and is the way I would have done it. Glad you got there - thanks for sharing!

Cheers!

MF.

[cognos05]

Hi MFGF,

Now I have to a do a count based on two childrens .

SO the row has  a territory and then column has  a product group .

SO now i have to count for each account each product if the sales is greater than 5000

count([H0 Sales] within set filter(children([ProductGroup]),tuple(currentMember([Sales Cube].[ShipToCustomer].[Customer]),[Rolling 12 Months],[Sales])>5000))

the above code does the count for a territory with each product group , but now if i need to do children of territory how can i achieve the same ?

so in the filter should return the account and product group combination whose sales is greater than 5000

so instead of childrn(productgroup) i should hae something like that will check for both children(productgroup) and children(currentMember(Territroy)

[MFGF]

Hi MFGF,

Now I have to a do a count based on two childrens .

SO the row has  a territory and then column has  a product group .

SO now i have to count for each account each product if the sales is greater than 5000

count([H0 Sales] within set filter(children([ProductGroup]),tuple(currentMember([Sales Cube].[ShipToCustomer].[Customer]),[Rolling 12 Months],[Sales])>5000))

the above code does the count for a territory with each product group , but now if i need to do children of territory how can i achieve the same ?

so in the filter should return the account and product group combination whose sales is greater than 5000

so instead of childrn(productgroup) i should hae something like that will check for both children(productgroup) and children(currentMember(Territroy)

Hi,

Wouldn't you just nest the next level down from Territory in the rows of your crosstab? That way you would see your counts for each child of each territory? Or am I missing something?

Cheers!

MF.

[cognos05]

I am showing the Accounts and territory one below other and not nesting ,as they wanted to see one below other.

So I have a union query on rows that has my accounts and Territroy and then i am doing a hierarchize on that .

And then in column count , I am checking the ordinal level of the element and writing the appropriate calculations.

Just wondering if I ever have to do a count on two childrens is that somethin its not possible without nesting

[cognos05]

Adding the nesting would automatically help , I would be very curious to know if we can count on two childrens as well for future use.
                 
                                     ProductGrouping1      CountGroup1
                                      A    B     C
                 Account1       1   2       5                    1
Territory 1  Account2       1   4       6                    2
                 Account3       0   0        4                   1
                 Total             2     6      15                  5

I am having one issue where         total of CountGroup1 shwing 5 for the territory now


Instead of 5 I need to calculate the penetration at territory level which is

5/3 (Product A,B,C)  *  3 Accounts = 5/3 *3

Product A,B,C - >  count(1 within set children(ProductGrouping1))

so to write this formula 5/3*3  where should i write this , will this go under countgroup1 dataitem saying if any member other than Account then do (5/3)*3

How do i refer the total count 5 and number of accounts 3 at a total item level in row.

But for 5  and number of children accounts I am having trouble when calculating at total interesction .

Any help is appreciated or any suggestions on how to calculate the penetration for the territories is really appreciated.

Thanks,

[cognos05]

Guys any help on this ,

for the total row ,how do i overwrite the calculation , I am stuck at this step .

[MFGF]

Guys any help on this ,

for the total row ,how do i overwrite the calculation , I am stuck at this step .

You could try adding another row or column that calculates the CountGroup total in the way you need it to, then unlock your structure and move it into the cell showing the incorrect total. I'm not sure if this will work with an OLAP source but it's worth a try.

Cheers!

MF.

[cognos05]

Hi MFGF,

Thanks for that approach , I have never done that, but looks like it would work for olap as well based on a sample  , I am giving it a try today and will keep you posted.

Thanks

[cognos05]

Hi MFGF,

I had something like belwo in cross tab
                             
                                   A B C     CountGrp1
               Acount1        2  4  4      2
Territoy1 Account2        2  5  6      2
              Account3        5 1  6       2
              Total             9   10 14    6


so what i did is  ceated a data item like

Total(currentMeasure within set AccounLevel) caled  customTotal and I made the define contents to yes on the cell where we have 6
Then unlocked the report and place the custom total.

so this total i would expect it to be 6 before going to next step , but i gives the total of all accounts and not the acounts within the  territory 1 .

so for my use case my calcualtion will be

Total(currentMeasue within acountlevel)/ (coun( 1 within set acount level *6)

but these calcualtions ae not looking at the acounts within the rep , it just does over all acounts.

Am I missing somethin.

[MFGF]

Hi MFGF,

I had something like belwo in cross tab
                             
                                   A B C     CountGrp1
               Acount1        2  4  4      2
Territoy1 Account2        2  5  6      2
              Account3        5 1  6       2
              Total             9   10 14    6


so what i did is  ceated a data item like

Total(currentMeasure within set AccounLevel) caled  customTotal and I made the define contents to yes on the cell where we have 6
Then unlocked the report and place the custom total.

so this total i would expect it to be 6 before going to next step , but i gives the total of all accounts and not the acounts within the  territory 1 .

so for my use case my calcualtion will be

Total(currentMeasue within acountlevel)/ (coun( 1 within set acount level *6)

but these calcualtions ae not looking at the acounts within the rep , it just does over all acounts.

Am I missing somethin.

Have you thought of using the rep member as a tuple?

[cognos05]

Rightnow

Rep - Rep level - Data Item
Account - Filter(Accuntlevel, sales>10000)

so when i add the custom total data item on the intersection of total and countgrp1
my calc  : total(currentMeasure within set Account)

where shoul i use the tuple(intersection) I have use tuple only in a data item , not brought in as a member.