I'm working on a report where I need data for current week, and the week before, and compare these two. I have a week column in my data, which are transactions, So my data looks something like:
Amount - Week
13 - 01
19 - 01
11 - 02
10 - 02
13 - 02
12 - 03
18 - 03
15 - 04
And I want to this as a result from the two most recent weeks and sum of Amount:
Week 03: 30
Week 04: 15
Now it easy to get the most recent week, just a maximum (Week for report), but when I want to select the 2nd largest I'm getting stuck.
I've tried to do a filter that is basically "Maximum( case when week = maximum(week) then null else week)", but either I have not figured out the syntax or I this approach does not work.
Other alternative which I tired was the rank() feature and then a query which selects rank in (1, 2) but for whatever reason I couldn't get this approach to work and only got the error
The function "to_char" is being used for local processing but is not available as a built-in function, or at least one of its parameters is not supported.
Which I believe has something to do with the aggregation (multiple records per occurence of week). Anyway I'm kind of stuck and the error messages aren't giving me any clues. Would very much appreicate some help!
RANK should work fine, but it may not work well if you try to get Cognos to do all of the work in one place. I thought I could filter on the ranked data item and set the Application property to After auto aggregation. But I got strange results.
Rather than trying to create one complicated solution, try breaking the problem into smaller, simpler components.
Define Query1
Data Items:
Week = [namespace].[query subject].[Week]
Amount = [namespace].[query subject].[Amount] with the detail aggregation set to Total
Rank = rank([namespace].[query subject].[Week])
Create Query2 and set Query1 as its source.
Data Items:
[Query1].[Week]
[Query1].[Amount]
Detail Filters:
[Query1].[Rank] <= 2
Use Query2 as the source for your list.