TM1 Forum

Hello,

I am working on a cube that has the following dimensions: Tickets (ticket number), Date (The day the data was updated), Status (open, closed, etc..), and Measures (Count, # of closed tickets, # of open, etc..). I created 2 attributes for the ticket dimension (Date Opened and Date Closed) both of which are string attributes and contain a date. The Date Opened attribute is never null but the Date Closed attribute can be null. I have a separate process that updates the attributes of the tickets everyday.

When I pull the data in I map a "1" to the cell value by the given status and date. So: Dec 1, 2009, ticket # 12345, Open, Count = 1.

I am trying to create a rule using the measure # of closed tickets = the sum of all the tickets on a given day that have a closed date = the day I am looking at. So If I select Dec 1, 2009, All Tickets, Closed I would like it to read the Date Closed Attribute and if the ticket was closed on that day it returns a 1 at the n level. Then when I select All Tickets, it sums it up.

I have tried numerous rules but I am missing something or I am just way off on the logic. One rule was:
['# of closed tickets'] = if(AttrS('Tickets',!Tickets, 'Closed Date') @<> '' & ATTRS('Tickets',!Tickets, 'Closed Date') @= DIMNM('Date',DIMIX('Date', !Date)), 1,0);

Could someone please point me in the right direction and also give me a best way to feed this rule?

Thanks!

~Kevin

If I understand correctly you have a string attribute 'Closed Date' in the Tickets dimension and you require a rule in your cube (which contains a date dimension) to check if the date matches the ticket's closed date and if so record a 1 against a count for tickets closed on that day ..

Your rule should then look like this:
['# of closed tickets'] = N: IF( AttrS('Tickets',!Tickets, 'Closed Date') @<> !Date, 1, 0 );

Note: this is an N level rule so it will need to be fed from a populated leaf cell measure against the same Ticket/Date combination.

IMO this is "static" data as opposed to "dynamic" or planning data that would be updated by a user and need to respond dynamically. Seem as you already have a process to update the open date and closed date attributes, why not update the cube at the same time? It seems to me there is no requirement for this to be rule driven.