Page 1 of 1

Count in rules

Posted: Thu Dec 14, 2017 4:42 pm
by Analytics123
Hi I have the following data

PersonResponsible Processor Month CaseID AverageTimeCOunt

ABC C1 Aug 2017 C1234 30days
ABC C2 Aug 2017 C1235 56 days
ABC C3 Aug 2017 C3456 0
ABC C4 Aug 2017 C4567 24 days
ABC C2 Aug 2017 C5685 0

Each column is a dimension and all data is loaded to the cube n level .

The Average time count caculation for consolidation is should be .

For that month sum the Averagetimecount and divide by number of cases for that month excluding 0 averagetimecount .

So in above case for aug 2017 for person ABC the the average time count should be (30+56+24 =110 )/3 (which is the count of case id's with averagetime count>0)

so for n level i load via ti , but for consolidation i have to use the rule .

Can anyone help me with the rule here .

[Averagetimecount]= C: (DB(!PersonResponsible , 'All Processor',!Months,'All cases'.'AverageTimeCount)/ Count(case with average time >0)

the first DB willgive me the total Averagetime count , but the division count part is what i am stuck at on how to write it .


Any help is appreciated .

Re: Count in rules

Posted: Thu Dec 14, 2017 8:56 pm
by Edward Stuart
The official IBM training has a similar challenge and uses a Hold element and a Count element.

Create an additional element alongside AverageTimeCount with a rule

[`NewCount`]=N: IF ( [`AverageTimeCount`] > 0 ) , 1, 0 ) ;

This will give the appropriate consolidated count to be used as a divisor. This could also be loaded from the source TI alternatively.

Then create an element to "hold" the AverageTimeCount (to avoid circular reference) and finally adjust the C level rule to divide the Hold value by the New Count

These new elements can be hidden from end users if they are an issue depending on trace calculation requirements

Re: Count in rules

Posted: Thu Dec 14, 2017 10:05 pm
by Analytics123
Hi ,
Thanks for the suggestion .

I have the below code

SkipCheck;
['NewCount' ] =N: IF( ['Average Turn Around Time Count' ] > 0,1,0);
['Hold' ] = C: DB('Dispute Data', 'All Regions', 'All Currencies', !Months, !Dispute Case ID , 'All Status', !Dispute Person Responsible, 'All Processors', 'All Reasons', 'All Root Cause Code', 'Average Turn Around Time Count') \ DB('Dispute Data', 'All R
egions', 'All Currencies', !Months, 'All Cases', 'All Status', !Dispute Person Responsible, 'All Processors', 'All Reasons', 'All Root Cause Code', 'NewCount');
Feeders;
['Average Turn Around Time Count' ] => ['NewCount'];
['Hold']=>['Average Turn Around Time Count','NewCount'];

But when I do zero supression my entire hold column disappears .

Can you please point out where is my issue with , is it with my hold feeders ?

Thanks,

Re: Count in rules

Posted: Fri Dec 15, 2017 6:17 am
by Edward Stuart
If you remove SKIPCHECK the values should resolve as expected.

You will need to resolve the feeders and I would suggest reading up on the Feeders documentation on Developerworks to get a better understanding

Values not consolidating need to be fed from the source values. Typically this means reversing the rules statement but the documentation will clarify

Re: Count in rules

Posted: Fri Dec 15, 2017 6:29 am
by silw
Analytics123 wrote: Thu Dec 14, 2017 10:05 pm Feeders;
['Average Turn Around Time Count' ] => ['NewCount'];
['Hold']=>['Average Turn Around Time Count','NewCount'];
You don't feed Hold element here.

Re: Count in rules

Posted: Fri Dec 15, 2017 4:35 pm
by Analytics123
Hi I am still getting the AverageTurnaround time removed on zero suppression .

Skipcheck;
['Cases with TurnAround' ] =N: IF( ['TurnAround Time' ] > 0,1,0);
['Average TurnAround Time' ] = C: DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') \ DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'Cases with TurnAround');

Feeders;
['TurnAround Time' ] => ['Cases with TurnAround' ];
['Average TurnAround Time' ] =>DB('Dispute Data', 'All Regions', 'All Currencies', !Months, 'All Cases', 'All Status', !Dispute Person Responsible,'All Processors' , 'All Reason', 'All Root Cause Code', 'TurnAround Time') ;

I assume my feeders are the issue here , but i am telling feed my Average turn around time if there is a value for turnaround time .

Any one who can help on this is appreciated .

Re: Count in rules

Posted: Sun Dec 17, 2017 5:51 pm
by Analytics123
Any help is appreciated .

['Average TurnAround Time' ] =>DB('Dispute Data', 'All Regions', 'All Currencies', !Months, 'All Cases', 'All Status', !Dispute Person Responsible,'All Processors' , 'All Reason', 'All Root Cause Code', 'TurnAround Time') ;

the above feeder is still gone while zero supression.

Re: Count in rules

Posted: Sun Dec 17, 2017 6:57 pm
by gtonkin
You may have confused yourself here. If you are deriving Average Turnaround time based on Cases with Turnaround Time, this (Cases with Turnaround Time) needs to be on the left I.e. feed from the dependent variable into the one being calculated.
HTH

Re: Count in rules

Posted: Sun Dec 17, 2017 10:15 pm
by Edward Stuart
Developerworks TM1 Feeders

When I'm having issues with Feeders I tend to set all elements to the N level and pick a single dimension to contain a consolidation. Verify that this works and then scale the feeders up.

However, as gtonkin says you need to feed from the correct element. A rough rule is that what is ruled from the left will need to be fed from the right

Are you feeding from the same cube? If so then you can use the square bracket notation for simplicity

Re: Count in rules

Posted: Mon Dec 18, 2017 2:56 pm
by Analytics123
Hi,
Okay this is what I got and I want some advise on how this works .

Working Rule and Feeders :

Skipcheck;
['Cases with TurnAround' ] =N: IF( ['TurnAround Time' ] > 0,1,0);
['Average TurnAround Time' ] = DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') \ DB('Di
spute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'Cases with TurnAround');

Feeders;
['TurnAround Time']=>['Cases with TurnAround'];
['Average TurnAround Time']=> DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time');


The above case works and zero suppression also works properly on Average TurnAround time column . But here I have not mentioned the rule to calculate to C level only , IF I say that then my Average Turn Aroundtime has an zero supression issue .

Not Working Rule and Feeders :

Skipcheck;
['Cases with TurnAround' ] =N: IF( ['TurnAround Time' ] > 0,1,0);
['Average TurnAround Time' ] =C:DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') \ DB('Di
spute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'Cases with TurnAround');

Feeders;
['TurnAround Time']=>['Cases with TurnAround'];
['Average TurnAround Time']=> DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time');


Please advise

Re: Count in rules

Posted: Mon Dec 18, 2017 3:02 pm
by gtonkin
Analytics123 wrote: Mon Dec 18, 2017 2:56 pm ...
Not Working Rule and Feeders :

Skipcheck;
['Cases with TurnAround' ] =N: IF( ['TurnAround Time' ] > 0,1,0);
['Average TurnAround Time' ] =C:DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') \ DB('Di
spute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'Cases with TurnAround');

Feeders;
['TurnAround Time']=>['Cases with TurnAround'];
['Average TurnAround Time']=> DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time');
...
Your second feeder is wrong - Turnaround time is the dependent variable i.e. without this, Average Turnaround Time cannot be calculated.
Your feeder is saying feed (the answer of Turnaround Time) from Average Turnaround Time - As Edward pointed out too, you "generally" need to start your feeder as the rule being switched around - what you have on the left being derived goes to the right as that is where you need the feeder.

Re: Count in rules

Posted: Mon Dec 18, 2017 3:22 pm
by Analytics123
Is there a syntax ,if I reverse it showing up syntax error .

DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') => ['Average TurnAround Time'];

same feeder reversed .

Thanks,

Re: Count in rules

Posted: Mon Dec 18, 2017 3:42 pm
by gtonkin
The syntax you had previously was correct but you need to have the source you are feeding from on the left and the target on the right. For now just switch the measure names.

I do suggest further reading on feeders as you will not get far in TM1 without a thorough understanding of them.

Re: Count in rules

Posted: Mon Dec 18, 2017 5:50 pm
by gtonkin
Posting this link to the IBM Developer Works site as there is a Proven Practice article dealing with Feeders that you and others should benefit from.

Re: Count in rules

Posted: Tue Dec 19, 2017 9:55 am
by Steve Rowe
Good link, should be required reading.

The other thing to consider is when it is appropriate to use a rule.

Given that the number that drives this is loaded from a TI, it would have been easy to populate the count value at the same time. Although this would use more RAM at rest, the eventual calculation would be faster.

Cheers,

Re: Count in rules

Posted: Wed Dec 20, 2017 12:12 am
by Edward Stuart
I know you have created another topic regarding the feeders but for completeness I will post here.

Source data:

PersonResponsible Processor Month CaseID AverageTimeCount

ABC C1 Aug 2017 C1234 30days
ABC C2 Aug 2017 C1235 56 days
ABC C3 Aug 2017 C3456 0
ABC C4 Aug 2017 C4567 24 days
ABC C2 Aug 2017 C5685 0

The calculation you would like to make is to calculate the TotalAverageTimeCount \ COUNTIF of AverageTimeCount

To resolve this I have created two new elements; 'CaseCount' and 'AverageTimeHold' and generated the following rules:

Code: Select all

SKIPCHECK ;
# Rules PULL values

# Perform Countif Function on AverageTimeCount
['CaseCount'] = N: IF ( ['AverageTimeCount'] > 0, 1, 0 ) ;

# Replicate AverageTimeCount to avoid Circular Reference on consolidation calculation
['AverageTimeHold'] = N: ['AverageTimeCount'] ;

# Sum AverageTimeCount into CaseCount via Hold element to avoid circular reference
['AverageTimeCount'] = C: ['AverageTimeHold'] \ ['CaseCount'] ;

FEEDERS ;
# Feeders PUSH values

# Feed CaseCount from AverageTimeCount
['AverageTimeCount'] => ['CaseCount'] ;

# Feed AverageTimeHold from AverageTimeCount
['AverageTimeCount'] => ['AverageTimeHold'] ;

Re: Count in rules

Posted: Wed Dec 20, 2017 3:33 am
by Analytics123
TThanks for taking time to give the sample code .

But Can you tell me whats wrong in my other post . I had done it after reading about feeders tutorial and would like to know why feeding is not happened . I have posted the rule tracer and check feeders screen as well . .

Instead of doing case count via rules, I used TI to load count value.
so now turnaround time and cases with turn around comes from ti , All i need is just division of this .


SkipCheck;
['Average TurnAround Time' ] = DB('Dispute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'TurnAround Time') \ DB('Di
spute Data', !Customers SoldTo Only, !Currencies, !Months, !Dispute Case ID, !Dispute Case Status, !Dispute Person Responsible, !Dispute Processor, !Dispute Reason, !Dispute Root Cause Code, 'Cases with TurnAround');


And I have a feeder :

['TurnAround Time'] =>['Average TurnAround Time' ] ;



Is any thing wrong here , it says not fed .

May be i can directly have based on the other post

['Average TurnAround Time' ] = ['TurnAround Time']\ ['Cases with TurnAround'];
And I have a feeder :

['TurnAround Time'] =>['Average TurnAround Time' ] ;


but in both cases the result is present but feeders says not fed and column disappears on zero suppression.

Can you tell me if there is some thing wrong in my rule or feeder . I have pasted the check feeders screenshot in my other post clearly .

Thanks,

Re: Count in rules

Posted: Wed Dec 20, 2017 9:28 am
by Steve Rowe
If you have persistent feeders on, which you probably do by default then I suggest turning it off, stopping the server, deleting the .feeder files and restarting. It is possible if you are have written the rule after loading the data that the feeders are not being triggered.

Others may disagree but I would suggest always developing with persistent feeders off.

Your rule and feeder look correct to me, assuming that ['TurnAround Time'] and ['Cases with TurnAround'] are both loaded items

['Average TurnAround Time' ] = ['TurnAround Time']\ ['Cases with TurnAround'];

['TurnAround Time'] =>['Average TurnAround Time' ] ;

Re: Count in rules

Posted: Wed Dec 20, 2017 2:19 pm
by Analytics123
Hi ,

Deleting the rules and restarting the server and recreating the rules fixed the issue . Thanks so much for all help .

Now I have a another question related . With same example ,

['Average TurnAround Time' ] = ['TurnAround Time']\ ['Cases with TurnAround'];

['TurnAround Time'] =>['Average TurnAround Time' ] ;

If I change my rule to apply for C level only

['Average TurnAround Time' ] =C: ['TurnAround Time']\ ['Cases with TurnAround'];

Then the zero supression will still not work , can anyone tell why it is . Is that how it should behave . Thanks

Re: Count in rules

Posted: Thu Dec 21, 2017 10:22 am
by Steve Rowe
The key thing with feeders to remember is all the action is at the N level, it is impossible to feed a consolidated cell.

Consolidated cells are fed internally when an N level under it becomes non-zero.

This is why your consolidation is not showing, since all the cells below it are zero the cell has not been internally fed and therefore is zero-supressed.

Simples! ;)