Maximum Sales by week

[GED4le]

Hello, I'm  very new here and Cognos is more of a hobby for me.

I would like some advice please on an expression that will show me the Maximum sales by week.

Sales contains individual transactions, i need the individual sales to be totalled by week then the data item only show the maximum of the total week, currently i can only get it to show the maximum individual transaction.

I know this is easy my head just cant figure it out right now!

Thanks you

[MFGF]

Hello, I'm  very new here and Cognos is more of a hobby for me.

I would like some advice please on an expression that will show me the Maximum sales by week.

Sales contains individual transactions, i need the individual sales to be totalled by week then the data item only show the maximum of the total week, currently i can only get it to show the maximum individual transaction.

I know this is easy my head just cant figure it out right now!

Thanks you

Hi,

Assuming you already have the Week defined as an item, it should be as simple as

maximum( [Sales] for [Week] )

In the above, [Sales] is your transaction amounts item, and [Week] is the item for your weeks.

Cheers!

MF.

[GED4le]

Thanks, yes thats what I had done but its showing the Max individual sale per week, not the total :'(

[cognostechie]

What exactly do you want?

Take some time to convert your thoughts into words and make up sentences which clearly describe your requirement even if you have to write a few more lines. I have been on this forum for a long time and have seen people not getting answers simply because they failed to convey their requirements clearly. As an example, I stopped giving advice to one person last week only because of this even though I knew the answer.

Provide an example to explain what your data looks like and what result you are expecting.

[dougp]

Think of this in terms of the SQL you are expecting Cognos to generate.  I think what you are looking for is something like the following.  I'm breaking it down into small chunks to make it easier to understand.

Code Select Expand

with cte as (
select [Week]
, sum([Sales]) as Sales
from MyTable
group by [Week]
)
select [Week]
, max([Sales]) as Sales
from cte
group by [Week]


So my first question would be:  Is [Sales] already summed?  Or are you expecting [Sales] to mean something else, like [namespace].[querysubject].[Sales]?  There's a difference.  Posting actual code can help clarify.

[MFGF]

Thanks, yes thats what I had done but its showing the Max individual sale per week, not the total :'(

So you want the total sales per week? Where does the Maximum part come in? Let's say you have total sales which are Week 1 - 30, Week 2 - 35, Week3 - 25, are you saying you only want to see Week 2 - 35 in your report?

In that case, set your data item in the report to be total([Sales] for [Week]), and add a detail filter (after aggregation) with the expression total([Sales] for [Week]) = maximum(total([Sales] for [Week]) for report)

Does this give you what you need?

Cheers!

MF.

[GED4le]

Thanks every one, it was as simple as i assumed, i just had not set the week total as its own data item rather i was assuming it would summarise it for me, it was a long week last week and fresh eyes and 2 mins problem sorted :)