Count in rules

Post Reply
Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Count in rules

Post by Analytics123 » Thu Dec 14, 2017 4:42 pm

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 .

Edward Stuart
Community Contributor
Posts: 214
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Count in rules

Post by Edward Stuart » Thu Dec 14, 2017 8:56 pm

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

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Thu Dec 14, 2017 10:05 pm

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,

Edward Stuart
Community Contributor
Posts: 214
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Count in rules

Post by Edward Stuart » Fri Dec 15, 2017 6:17 am

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

silw
Posts: 15
Joined: Wed May 25, 2011 12:39 pm
OLAP Product: TM1
Version: 1022-FP*
Excel Version: 2010-2016

Re: Count in rules

Post by silw » Fri Dec 15, 2017 6:29 am

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.

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Fri Dec 15, 2017 4:35 pm

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 .

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Sun Dec 17, 2017 5:51 pm

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.

User avatar
gtonkin
MVP
Posts: 600
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Count in rules

Post by gtonkin » Sun Dec 17, 2017 6:57 pm

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

Edward Stuart
Community Contributor
Posts: 214
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Count in rules

Post by Edward Stuart » Sun Dec 17, 2017 10:15 pm

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

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Mon Dec 18, 2017 2:56 pm

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

User avatar
gtonkin
MVP
Posts: 600
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Count in rules

Post by gtonkin » Mon Dec 18, 2017 3:02 pm

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.

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Mon Dec 18, 2017 3:22 pm

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,

User avatar
gtonkin
MVP
Posts: 600
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Count in rules

Post by gtonkin » Mon Dec 18, 2017 3:42 pm

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.

User avatar
gtonkin
MVP
Posts: 600
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.1
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: Count in rules

Post by gtonkin » Mon Dec 18, 2017 5:50 pm

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.

User avatar
Steve Rowe
Site Admin
Posts: 1771
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Count in rules

Post by Steve Rowe » Tue Dec 19, 2017 9:55 am

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,

Edward Stuart
Community Contributor
Posts: 214
Joined: Tue Nov 01, 2011 10:31 am
OLAP Product: TM1
Version: All
Excel Version: All
Location: Manchester
Contact:

Re: Count in rules

Post by Edward Stuart » Wed Dec 20, 2017 12:12 am

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'] ;

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Wed Dec 20, 2017 3:33 am

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,
Attachments
CheckFeeders.png
CheckFeeders.png (15.88 KiB) Viewed 1091 times

User avatar
Steve Rowe
Site Admin
Posts: 1771
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Count in rules

Post by Steve Rowe » Wed Dec 20, 2017 9:28 am

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' ] ;

Analytics123
Posts: 127
Joined: Tue May 23, 2017 12:45 pm
OLAP Product: Tm1
Version: 9.5
Excel Version: 2010

Re: Count in rules

Post by Analytics123 » Wed Dec 20, 2017 2:19 pm

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

User avatar
Steve Rowe
Site Admin
Posts: 1771
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Count in rules

Post by Steve Rowe » Thu Dec 21, 2017 10:22 am

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! ;)

Post Reply