cancel
Showing results for 
Search instead for 
Did you mean: 

Reporting need : showing min,max & diff of first row total with others

Former Member
0 Kudos

Hello ,

I have 2 reporting needs , in which report layout required

is as below.

Wk1 Wk2 Wk3 Wk4 Total Diff

Wk1 1 2 4 2 9 0

Wk2 _ 3 2 1 6 -3

Wk3 _ _ 2 2 4 -5

Min 1 2 2 1

Max 1 3 4 2

Problem i have is in showing the difference. Total comes automatic.

How can i compute difference with respect to first row.

For Min & Max , when i create a formula, and set its Calculate Result as

property to be min/max,

I get the min /max value at the bottom. But then the values in data rows repeat like

Wk1 Wk2 Wk3 Wk4 Total Diff

Min 1 2 4 2 9 0

Max 1 2 4 2 9 0

Min _ 3 2 1 6 -3

Max _ 3 2 1 6 -3

Min _ _ 2 2 4 -5

Max _ _ 2 2 4 -5

Min 1 2 2 1

Max 1 3 4 2

This i don't want. I want a single row for min & max. How can i do it.

Thanks for your help as soon as possible.

PS.. I have put this '_' to show that value is blank in this field.. which is fine

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hello,

Try using exception aggregation rather than using the calcualte result.

With exception aggregation, you should be able to calculate the max/min/... as cell values and then formulas would work on the cells.

Cheers

Aneesh

Former Member
0 Kudos

Thanks Aneesh,

With this, my max & min problem has been solved .

But still, problem of difference exist..

I again explain what more is needed

For example

WK1 WK2 WK3 Total Difference (with first total)

WK1 4 5 2 11 0

WK2 2 5 7 -4

WK3 12 12 1

Thanks for your reply in advance . With this solving

I will be able to build 10 reports

Former Member
0 Kudos

Hello Deepali,

Difference is between which two columns? Cannot understand it from the example yuo have given.

Regards

Aneesh

Former Member
0 Kudos

Aneesh ,

User wants to see the difference of overall sum of each calweek with calweek1.

example. calweek 1 with calweek1,calweek2 with calweek1 calweek 3 with calweek 1 ,Calweek 4 with calweek1 and so on.

Is it now more clear

WK1 WK2 WK3 Difference (with first total)

CALWK1 4 5 2 0

CALWK2 2 5 -4

CALWK3 12 1

Here , in rows, first week total is 11. So difference with first = 0.

Second week total is 7. So difference with first (7-11 = -4),

For third row, 12-11 = 1.

I hope its clear. I am waiting for your reply..

Former Member
0 Kudos

Deepali,

The only way of achieving calculations across rows is to use the cell editor.

Cheers

Aneesh

Former Member
0 Kudos

Hi Aneesh, Could you through some light on. how to use cell editor for this purpose.

I am not very comfortable with it.

When i create structures. can i still get all

the rows & columns of different weeks ??

Thanks & Regards

Deepali

Former Member
0 Kudos

Deepali,

To use the cell editor you need to have two structures in your query. Create a structure in the rows with the weeks and a structure in the columns with the key figures. Cell editor will become enabled in the toolbar in the query designer right after the conditions button.

In the cell editor you can then define a formula wherever you like.

Cheers

Aneesh

Former Member
0 Kudos

Aneesh, that much i know (how to invoke cell editor). But doing this gives only single value

My weeks in rows could be 'n'.. & and i need to do this calculation for all rows.. wrt 1st row total.

Former Member
0 Kudos

Mis-understanding on my part. I thought that each row is a difference from previous row. Cell editor cannot be used with flexible selection.

If the difference is only w.r.t. the first row, then create another key figure and restrict this to the seelctions being used for the first row. Select constant selection on this restriction. It should give the same value in all the rwos after that.

Hope it works.

Former Member
0 Kudos

Aneesh,

I am still not getting the value i want.

I want in last column '5' in all rows. But what i get is

Week Sum First Value

01.2005 5 15

02.2005 4 15

03.2005 3 15

04.2005 2 15

05.2005 1 15

Overall Result 15 15

What i have done is create a calculated/restricted key figure.

& set property of that as first value

& placed all restriction of selection & set as constant selection.

I even tried putting restriction of the week by fixed value '01.2005'.

I think there is still something missing.

Former Member
0 Kudos

Deepali,

What is the result in the last column with a constant selection of 01.2005? It should be 5. Right click on 01.2005 in the restriction of the key figure and select constant selection.

if it si 5, then create a difference colum between the sum and this last column.

Regards

Aneesh

Former Member
0 Kudos

This is the problem. Last column with constant selection of 01.2005 also doesn't give 5. It gives 15.

I have selected this constant selection already with restriction.

I seem to be at dead end

Former Member
0 Kudos

Deepali,

I just tried it out and it is working.

Data:

Calendar Year/Week | Number | Week1 No.

01.2004 | 26 | 26

02.2004 | 16 | 26

03.2004 | 19 | 26

04.2004 | 17 | 26

Overall Result | 78 | 26

Query Definition is:

Rows: Cal Year/Week

Columns: Number (Key Figure 0NUMBER)

Week1 No. (Key Figure 0UMBER) restricted to Cal year week 01.2005. After restricting right clicked on 01.2005 in the key figure restriction and selected Constant Selection.

Regards

Aneesh

Former Member
0 Kudos

Aneesh, Problem solved.

I created a key figure with exception aggresgation as first value

& it solved my problem. I don't know how/why... but

the guess has solved the problem,

Thanks a lot for your answers.

Regards

Deepali

Former Member
0 Kudos

Can you elaborate on how to use "exception aggregation"? I have a similar issue. I have rebates transaction data. Each line contains the % for rebate calculation. The % can be different depending on when the transaction occured. As a customer's sales improve, so does their rebate %. In the report I want to show the actual percentage (accrued rebate $ / net sales $) and "current" percentage (max value of the ZCONDRATE field). I've tried using "calculate result as..." and using the forumula functions MAX() and MAX0(), but nothing works. Any ideas? Thanks!

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Arun,

I am building query using query designer. But have ultimately to show it using Web Application designer

on web.

former_member184494
Active Contributor
0 Kudos

Deepali,

Are you using the web or Excel for reporting ?

There are workarounds to achieve what you want depending on what is the method you use to broadcast queries.

Arun