Link to home
Start Free TrialLog in
Avatar of Sigurdur Hergeirsson
Sigurdur Hergeirsson

asked on

Using character type for MODEL dimension causes rules using CV() to repeat results - why ?

I am using MODEL clause to calculate balance and compound interest.
Using generated date converted to YYYYMM as key to dimension I get balance always repeated each 12 months.  
Can someone explain why ?

By using the generated seqence number  directly as the dimension-key fixed the problem.  


Simplified sql of both cases:
--Here balance reapeats every 12 months
SELECT * FROM (
  SELECT    TO_CHAR( ADD_MONTHS(DATE '2000-01-01',  SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual  connect by level <= 100)
ORDER BY YEARMON
)
MODEL
dimension by (YEARMON)
measures (BASE, BAL)
RULES (
      BAL[ANY] ORDER BY YEARMON =   nvl(BASE[CV()],0) +   nvl(BAL[CV()-1],0)
)  ORDER BY YEARMON


--Here balance get increased until lastrow (as disired)
SELECT * FROM (
  SELECT   SEQNR,  TO_CHAR( ADD_MONTHS(DATE '2000-01-01',  SEQNR),'YYYYMM') AS YEARMON, 1 AS BASE , 0 AS BAL FROM (select LEVEL-1 AS SEQNR from dual  connect by level <= 100)
ORDER BY SEQNR
)
MODEL
dimension by (SEQNR)
measures (YEARMON,BASE, BAL)
RULES (
      BAL[ANY] ORDER BY SEQNR =   nvl(BASE[CV()],0) +   nvl(BAL[CV()-1],0)
)  ORDER BY YEARMON
Avatar of PortletPaul
PortletPaul
Flag of Australia image

Seems that the model recognizes the YEARMON dimension as being date based
ASKER CERTIFIED SOLUTION
Avatar of Sigurdur Hergeirsson
Sigurdur Hergeirsson

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial