CODEWITHSUNDEEP
cognoscognos-bicognos-11
Andrey Hartung
Andrey Hartung

Reputation: 853

COGNOS - How to sum the last 12 row values

I need to sum the last 12 of a value for EVERY ROW. On SQL this looks like it:

sum(value) over (partition by field1, field2
            order by field3 asc rows BETWEEN 11 preceding AND CURRENT row
        ) AS total_acum

I can't do that on the database and need to do that on the Cognos... Any tip?

Obs.:

@C'est Moi tells to use the code:

running-total ( QUANTITY
for
Period ( _add_months ( order_day_date, -12) , order_day_date)
)

But when i do that, i got this error messages:

Try 1 My code (with ;):

running-total ( [total] for period( _add_months ( [date]; -12) ; [date]) )

Try 1 Error:

XQE-V5-0017 Erro de sintaxe V5 localizado para o item de dados ''query_field_name'' da consulta ''cognos_query'', token inválido ";" localizado após "running-total ( [total] for period( _add_months ( [date]; -12) ". CAF-WRN-2082 ... O CAF ... log com o SecureErrorID:2021-01-18-12:36:58.114-#874

Try 2 My code (with ,):

running-total ( [total] for period( _add_months ( [date]; -12) , [date]) )

Try 2 Error:

XQE-V5-0017 Erro de sintaxe V5 localizado para o item de dados ''query_field_name'' da consulta ''cognos_query'', token inválido " " localizado após "running-total ( [total] for period( _add_months( [date]; -12) ,". CAF-WRN-2082 Ocorreu um erro. Entre em contato com o administrador. O CAF registrou o erro completo no log com o SecureErrorID:2021-01-18-12:53:54.959-#891

Edit 3: I'm starting to think that this is some kind of configuration that is'nt right and dont let me use dimentional functions... Does anyone know something about that?

Upvotes: 0

Views: 1066

Answers (1)

C'est Moi
C'est Moi

Reputation: 326

moving-total would be what you would want. It would require that your report is ordered.

Running total for period might be what you want.

running-total ( QUANTITY

for

Period ( _add_months ( order_day_date, -12) , order_day_date)

)

Upvotes: 1

Related Questions