Count totals in active form

[UN2]

All I would like to be able to have formulas applied to child elements in an excel active form or even a rule.

I have tried formula just to count total child elements and dont even get that right

['Count']=N:IF(['revenue']>0,1,STET);

from another post gives me large numbers due to multiple sales transactions making up the revenue.

I also didnt get the post about using the following as a rule for unique values

SKIPCHECK;
['All Customers','Count'] = ConsolidateChildren('Customer');
['Count']=IF((['Sales'])>0 & ELLEV('Customer',!Customer)=0,1,STET);

FEEDERS;
['Sales' ]=> ['Count'];

Im happy to just do this in excel but I cant get the "green totals value" as an active form applies the same formula to all cells when it is recalculated.

Every month new accounts are added, so we run the report monthly.


any ideas are appreciated.

at the moment we run the report and manually every month, count the number of yellow totals which is time consuming for all of the sales reps.

[tomok]

I think one of the reasons no one has helped you is 1) your screen shot doesn't make any sense (I don't know what logic you are using to populate the Count column as it's not apparent from the picture) and 2) you haven't provided any details behind the cube structure.

[Acc_Moonwalk]

Hi,

You don't have to write any rule in TM1, Since you are trying to achieve this in active form, you just need to write a Calculation in Excel like (if Revenue Cell >0,1 0)
it works.

Cheers

[UN2]

Hi

thanks for your replies

Consider the first column as the list of sales staff with their accounts they own.
The second column is the total sales revenue for october. (obtained from the sales cube based on all the sales for that account for october)
The third column is the total sales revenue for the year.(obtained from the sales cube based on all the sales for that account for the full year)
The fourth column is simply (if column B=Column C) then put 1 otherwise put 0, this is fine
The issue i have is being an active form I cannot get the totals for the each salesperson (ie the green cells) .

in excel this is normally done by "=sum(D3:D5)" which will calculate the total. but since the form size changes every month the formula wont work. ie the next month salesperson 1 may have 5 accounts so the sum should be "=SUM(D3:D7) etc

I need a way to sum the column of 1,s and 0's per salesperson.

[tomok]

Now that I can see what it is you are actually trying to do, which is to generate a count of the customers who bought something for the first time this month, instead of "get a total of the yellow section", then the answer is quite simple, do this with a rule. I don't know what your time dimension looks like so I am going to propose adding an attribute to each month called "YTD_Period" and in that you will store the element name that corresponds to the YTD rollup through that month. Here is the rule (I don't know your dims or where the Period dim is but here goes):

Code: Select all

['Count']=N:IF(DB('CubeName',!Dim1,!Dim2...,!Dimx,!Period,'Sales')=DB('CubeName',!Dim1,!Dim2...,!Dimx,ATTRS('Period',!Period,'YTD_Period'),'Sales'),1,0);

FEEDERS;
['Sales']=>['Count']

The feeder statement is going to over-feed but proabably not a big deal.