Filter issue in FM and report

[Marvin Falentin]

Hey everyone,

I'm trying to show two Performance indicators in a report: 1) "totalcpu" 2) the average of the "totalcpu" for the last 30 month in two different columns.
In the Report the viewer will be able to set a filter on the date, normally yesterday.

E.g. the report shows you the "totalcpu" for the timeframe from yesterday to today compared to the totalcpu average for the last 30 days.

My problem is because of the set filter in the report the average on the last 30 days will always be "overriden". How can I actually translate this properly?

In SQL I'd use something like Partition over maybe but I'm not sure how to do this in Cognos FM. I'd very much appreciate a modelling solution rather than a change in the database like a SQL script.

Kind regards,

Marvin

[Marvin Falentin]

Hey everyone,

I just wanted to give an update on my problem:

What I come up with is "average ( TOTALCPU for RUNSTARTTIMESTAMP < _add_days(current_date; - 30) )", this actually shows what I expect / want to see, at least in FM e.g. dates like "Mar 15" or "Mar 21".

My question is why do I have to use "<" rather than ">"? Maybe my brain is lagging out here but should it not be ">" because I want the be able to see the values for the last 30 days? If I use anything else than "<" it will show me start dates from 2015 and 2016 for some reason.

Another problem is when I want to use the calculated value ("ACPU" for average totalcpu) it again shows different values than what I would expect and it even changes the other columns to "average" as well.

Any suggestions?

Kind regards,

Marvin