FUNCIÓN YTD

50 views
Skip to first unread message

moises junior lozada taboada

unread,
Mar 9, 2016, 9:05:36 PM3/9/16
to Pentaho Community
Buenas noches
La siguiente consulta mdx devuelve el monto desembolsado acumulado en el rango del año,hasta ahí todo bien

sum(YTD([TIEMPO.CALENDARIO].CurrentMember),[Measures].[MONTO DESEMBOLSADO])

quisiera saber como se puede realizar para que me siga sumando mas años.
por ejemplo 2015 y 2016 

Nelson Sousa

unread,
Mar 10, 2016, 5:01:35 AM3/10/16
to pentaho-...@googlegroups.com

YTD( [Some time member] ) is shorthand for

( [First day of year] : [Some time member] )

So, if your dimension is organized as [Time].[Year].[Month].[Day], you can replace

YTD( [Time].CurrentMember ) by ( [Time].[2016].[1].[1] : [Time].CurrentMember );

Which means you can also move N years back by using

( [Time].[2015].[1].[1] : [Time].CurrentMember )

to give you 2 years;


If you want to Parametrize the year you can alternatively use

[Time].[2016].Lag(3).FirstChild.FirstChild instead of [Time].[2013].[1].[1];

And even nicer, if you don't know the year of [Time].CurrentMember and want to calculate it in a more closed form,


( Ancestor( [Time].CurrentMember, [Time].[Year] ).Lag(3).FirstChild.FirstChild : [Time].CurrentMember )

to calculate all date members from the 1st day of the year 3 years ago until the CurrentMember;

So, if [Time].CurrentMember is 2016-03-10, that's the same as writing

( [Time].[2013].[1].[1] : [Time].[2016].[3].[10 )

But if [Time].CurrentMember is 2010-12-25, this returns the range

( [Time].[2007].[1].[1] : [Time].[2010].[12].[25] )



Cheers,






Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/2eaa5da7-ef2e-403f-951f-bab8a03fc27a%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

MOISES612

unread,
Mar 10, 2016, 10:30:51 AM3/10/16
to Pentaho Community
Gracias por  tus comentarios pero te comento que el incoveniente que tengo es el siguiente,que se explica en la imagen adjunta
IMG-EJEMPLO.jpg

Nelson Sousa

unread,
Mar 10, 2016, 10:33:04 AM3/10/16
to pentaho-...@googlegroups.com

Can you please provide an alternative context using steelwheels, so it can be written and tested by other people? I can't write it in a schema I don't know, and most likely I wouldn't be able to write it correctly without testing.


Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/b5ea04e4-f2c6-4207-9c6d-444d21b063b4%40googlegroups.com.

MOISES612

unread,
Mar 10, 2016, 10:54:45 AM3/10/16
to Pentaho Community
hola,necesito crear un miembro calculado  , te adjunto cuadro para explicar un poco mejor 

201501 201502
MES MONTO DESEMBOLSADO SALDO VENCIDO SALDO CASTIGADO MONTO DESEMBOLSADO SALDO VENCIDO SALDO CASTIGADO
201501 15,079,147.34 0.00 0.00      
201502 .00 0.00 0.00 16,209,177.70 0.00 0.00
201503 .00 19,618.99 0.00 .00 0.00 0.00
201504 .00 31,708.08 0.00 .00 20,461.27 0.00
201505 .00 76,821.55 0.00 .00 31,246.06 0.00
201506 .00 150,886.21 0.00 .00 80,700.15 0.00
201507 .00 261,859.28 0.00 .00 173,588.20 0.00
201508 .00 412,907.77 0.00 .00 320,799.54 0.00
201509 .00 519,822.85 0.00 .00 506,558.81 0.00
201510 .00 543,904.29 0.00 .00 600,895.66 0.00
201511 .00 693,037.71 0.00 .00 675,743.46 0.00
201512 .00 741,757.05 48,447.61 .00 666,736.68 17,432.31
201601 .00 741,415.81 48,447.61 .00 744,945.87 17,432.31
201602 .00 751,292.25 48,447.61 .00 784,925.13 17,432.31

el miembro calculado va hacer la suma SALDO VENCIDO+ SALDO CASTIGADO entre el 
MONTO DESEMBOLSADO.

(SALDO VENCIDO+ SALDO CASTIGADO)/
MONTO DESEMBOLSADO

El incoveniente que tengo es que quiero que divida sobre el monto desembolsado  donde el periodo(columna) sea igual a l mes de comparacion (fila)
espero me pueda ayudar 






Nelson Sousa

unread,
Mar 10, 2016, 11:12:54 AM3/10/16
to pentaho-...@googlegroups.com

You need to calculate your [Measures].[Desembolso3] as the Aggregate of the whole date range. Then use that on the formula.

See above reply, it has what you need to create the calculated member.

Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/8147336d-7ffe-4879-a1b5-2ebf34cc0914%40googlegroups.com.

MOISES612

unread,
Mar 10, 2016, 7:47:50 PM3/10/16
to Pentaho Community
buenas noches Nelson
segui tus indicaciones pero se me presenta el siguiente inconveniente al declarar el siguiente miembro calculado:
capital acumulado

SUM({[TIEMPO.CALENDARIO].currentMember.parent.FirstChild:[TIEMPO.CALENDARIO].currentMember}, [Measures].[SALDO CAPITAL])

me suma por trimestre  como se muestra en la imagen .

La dimensión tiempo es la siguiente:
<Dimension name="TIEMPO" type="TimeDimension"  highCardinality="false">
    <Hierarchy  name="CALENDARIO" hasAll="true" primaryKey="FECHA_SK">
      <Table name="BIDTIEM" schema="DWH"/>
      <Level name="ANIO" uniqueMembers="false" column="ANYO_ID" levelType="TimeYears" type="Numeric"></Level>
 <Level name="TRIMESTRE" uniqueMembers="false" column="TRIM_DESC" levelType="TimeQuarters" type="String"></Level>
      <Level name="MES" uniqueMembers="false" column="MES_ID" levelType="TimeMonths" type="String"></Level>
      <Level name="FECHA" uniqueMembers="true" column="FECHA_ID" levelType="TimeDays" type="Date"></Level>
    </Hierarchy>
  </Dimension> 

espero me puedas ayudar y sume el total acumulado no importando los meses que pases .
Gracias por tu apoyo





El miércoles, 9 de marzo de 2016, 21:05:36 (UTC-5), MOISES612 escribió:

Nelson Sousa

unread,
Mar 11, 2016, 6:01:39 AM3/11/16
to pentaho-...@googlegroups.com


Replace by 

SUM({[TIEMPO.CALENDARIO].[MES].[201501]:[TIEMPO.CALENDARIO].currentMember}, [Measures].[SALDO CAPITAL])


Parametrize as needed.


Nelson Sousa
Business Intelligence Consultant


--
You received this message because you are subscribed to the Google Groups "Pentaho Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to pentaho-commun...@googlegroups.com.
To post to this group, send email to pentaho-...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/pentaho-community/357bda4d-b3ba-4450-889a-e0836f290867%40googlegroups.com.
Reply all
Reply to author
Forward
0 new messages