Consolidate with the last day of the date dimension

[ablancof]

Hi people, how are you? Im new using Tm1 , i have experience using another tools like Cognos Transfomer but Tm1 is too diferent. I really need your help:

I have a cube with a date dimension (year->quarter->month->day). I have a measure called num_visits . The rollup for this measure must be :
- To the month level i have to select the last day of the month
- to higher levels must be a sum.

i did this with a rule but i got zero values in all cells (using elcomp function)

['NUM_VISITS']=DB('Cubo_Diario',!CDV,!Cliente Asociacion,!Cliente Negocios,!Cliente Nielsen,ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Fecha Devolucion,!Fecha Factura,!Geografia CDV,!Geografia Cliente,'NUM VISITAS',!Planta,!Producto,!Tipo,!Vendedor);

i have only a cube called Cubo_Diario

Any suggestions, THANKS in Advance

[Duncan P]

This rule is not only applying to the consolidated periods, it is also applying to the day level. At the day level ELCOMPN is returning 0, and ELCOMP( dim, element, 0 ) will always return an empty string. The DB will thus return 0. The rule is possibly working OK for the higher levels (but see below) but the values it is pulling are all 0.

I am assuming that the mismatch between ['NUM_VISITS'] on the left and 'NUM VISITAS' on the right is due to you partially translating it. If not and there is not a 'NUM VISITAS' element that will also be contributing to the problem. A wrong element in a cell reference (e.g. ['element']) will get reported as an error but in a DB statement it will just give you a 0 result.

What you need to do is to restrict the statement to consolidated cells. You do this with the C: qualifier which goes immediately after the = sign.

e.g.

Code: Select all

['NUM_VISITS'] = C: DB('Cubo_Diario',!CDV,!Cliente Asociacion,!Cliente Negocios,!Cliente Nielsen,ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Fecha Devolucion,!Fecha Factura,!Geografia CDV,!Geografia Cliente,'NUM VISITAS',!Planta,!Producto,!Tipo,!Vendedor);

However if you have other hierarchies this will not be enough. You will need to make sure that when you are at day level in the Date dimension the rule does not apply - even though you may be at a consolidated level on another dimension. You achieve this with a conditional statement

e.g.

Code: Select all

['NUM_VISITS'] = C: IF( 0 = ELLEV( 'Date', !Date ), CONTINUE, DB('Cubo_Diario',!CDV,!Cliente Asociacion,!Cliente Negocios,!Cliente Nielsen,ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Fecha Devolucion,!Fecha Factura,!Geografia CDV,!Geografia Cliente,'NUM VISITAS',!Planta,!Producto,!Tipo,!Vendedor) );

If your Date coordinate is at level 0 (leaf) then this skips to the next statement that applies.

Hope this helps.

[ablancof]

Hello thank you very much for your reply.
You're right, i made ​​a mistake in translation. Excuse me for my english.
Like you said put the qualifier C:. And get results in the month but at the level day i have only zero values​​.

i have a question, im writing this rules in the cube "Cubo_Diario", i mean against the same cube, no problem with this?
when i write

Rule in Cubo_Diario:
['NUM VISITS']=C:DB('Cubo_Diario',!CDV,!Cliente Asociacion,!Cliente Negocios,!Cliente Nielsen,ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Fecha Devolucion,!Fecha Factura,!Geografia CDV,!Geografia Cliente,'NUM VISITS',!Planta,!Producto,!Tipo,!Vendedor);

I get zero values but if i write using another measure, this put values in the month level

['% ACTIVACIÓN']=C:DB('Cubo_Diario',!CDV,!Cliente Asociacion,!Cliente Negocios,!Cliente Nielsen,ELCOMP('Date',!Date,ELCOMPN('Date',!Date)),!Fecha Devolucion,!Fecha Factura,!Geografia CDV,!Geografia Cliente,'NUM VISITAS',!Planta,!Producto,!Tipo,!Vendedor);

i left an image with a cube view. I dont have data in day level but higher levels yes, why is hapenning this?


Again , thank you so much

[Duncan P]

This is because you did not use the condition described in the second part of my reply. The cells you are looking at are consolidated even at the day level because they apply to e.g. "All_Producto" and "All_Tipo".