5 Replies Latest reply on Mar 31, 2017 1:59 AM by Prasenjeet Acharjee

# calculation across a row and coloumn

Hi,

I am stuck with a question which my colleague asked my help for. I have a table as shown below.

In the above table, for each product (for example B2L57B), i have a debit and a credit (-1260 in April and 1260 in June). I want to filter this whole table so that my table has only those data points for which the net sum for debit and credit (-1260 in April and 1260 in June) is 0.

a second variation of this case is product no. B0T87PA, where sum of (-6000, 22365, 16365) is 0.

So in summary,

1. i am asked to filter the table to include only those data points for which the net sum is 0 (consider the 2 situations mentioned above).

2. Sum of only the +ve values for each coloum.

I am attaching the twbx file as well with this post. Any help will be greatly appreciated. Thanks in advance.

• ###### 1. Re: calculation across a row and coloumn

I can do each one individually as in the attached.

2 of 2 people found this helpful
• ###### 2. Re: calculation across a row and coloumn

Hi

I'm not totally sure I know what you if what you want is a single value that represents the 12 month prior to the selected date or whether you are trying to compare yoy

in any event the attache workbook has a solution for the previous 12 months to a parameter -

The process was to:

1. create a new parameter New Date Selector
1. This parameter has a Data Type DATE
2. Format Month Year
3. and the values are a list generated from Order Date
2. Create a calculated field for 12 month sales
1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([Sales])
3. Create a calculated field for 12 month profit
1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([profit])
4. Create a calculated field for 12 month discount dollars
1.   IF datetrunc('month',[Order Date])> dateadd('month',-12,[new date selector]) and datetrunc('month',[Order Date])<=[new date selector] then zn([Sales]*zn([Discount])
5. Create a calculated field for the new discount rate
1. { FIXED :  (sum([12 month \$ discount]))}/{ FIXED :(sum([12 month sales]))
6. Create a calculated field for the new profit ratio
1. { FIXED :  (sum([12 month profit]))}/{ FIXED :(sum([12 month sales]))}

It should product the view shown below:

Let me know if this helps

Jim

1 of 1 people found this helpful
• ###### 3. Re: calculation across a row and coloumn

Hi John,

Thanks for your prompt response. Your solution almost solves my 1st part of the problem. Can you suggest a way through which i can get a sum of only the positive values in the table?

• ###### 4. Re: calculation across a row and coloumn

Hi John,

It will be of great help to me if you can find some time to explain to me how you arrived at the individual solutions.

• ###### 5. Re: calculation across a row and coloumn

Hi Jim,

Thanks for your response and effort that you put in to solve my problem. What i was looking for is more inline with John's answer.

Warm regards,

Prasenjeet