
Problems with calculated expressions in a table
Nilesh Gangurde May 10, 2012 7:37 AM (in response to carmenhm)Hi Carmen,
Attach the sample data. and what is the requirement please elaborate.
Regards,
Nilesh Gangurde

Re: Problems with calculated expressions in a table
Ravi Kumar May 10, 2012 7:40 AM (in response to carmenhm)Hi,
Can you eloberate your requirement.
Regards
Ravikumar

Re: Problems with calculated expressions in a table
carmenhm May 10, 2012 7:56 AM (in response to Ravi Kumar )I load the data from an Oracle Database. I attach part of the script. What I want is to show in the Pivot Table:
Product Code, Product Name, Units ordered, Amount between to dates (DateFrom and DateTo)
But, I need to Calculate in each line Amount/Total Amount(%), Total Amount up to the line, Total Amount up to the line/Total Amount (%)
Thank you
Carmen

Data.doc 24.0 K

Problems with calculated expressions in a table
Nilesh Gangurde May 10, 2012 8:07 AM (in response to carmenhm)Hi Carmen,
Create the sample data file as u find the data in the oracle after the select query .
and attach it.
Regards,
Nilesh Gangurde

Re: Problems with calculated expressions in a table
carmenhm May 10, 2012 10:50 AM (in response to Nilesh Gangurde )A sample of the Oracle query is
SELECT P.NCODPRODUCT, P.SDESPRODUCT, SUM (NNUMUNIDADE) UNITS, SUM(NIMPTOTCOST) AMOUNT
FROM ND_H_MOVIMIENTO MOV INNER JOIN ND_D_PRODUCTO P ON MOV.NCODHOSPITA = P.NCODHOSPITA AND MOV.NCODPRODUCT = P.NCODPRODUCT
WHERE MOV.NCODHOSPITA = 10 AND DFECPEDIDO BETWEEN '01/11/2011' AND '30/11/2011' AND P.NCODTIPPROD=1 AND P.NCODNIVEL1 = 7
GROUP BY P.NCODPRODUCT, P.SDESPRODUCT
ORDER BY NCODPRODUCT
And the results are shown in the attached Excel file.

Sample.xls 31.0 K

Re: Problems with calculated expressions in a table
Nilesh Gangurde May 11, 2012 3:04 AM (in response to carmenhm)Hi Carmen,
Find the attachement there i caluculated the values which you gave at first post.
Regards,
Nilesh Gangurde

Test.qvw 144.8 K

Re: Problems with calculated expressions in a table
carmenhm May 11, 2012 8:12 AM (in response to Nilesh Gangurde )Nilesh,
I see it Works nicely in your file. I have to get data for a time frame, so I have adapted your expresions.
In amount I have defined SUM ( {$}IMPORTE_MOVIMIENTO),0,RowNo() ))
and it doesn’t work. I get the same results than in Amount.
What am I doing wrong?
Thanks in advance
Carmen

Problems with calculated expressions in a table
Nilesh Gangurde May 12, 2012 1:26 AM (in response to carmenhm)Hi Carmen,
I think you are confused.
1. If you want to calculate Amount, Give the expression as mention below:
sum(IMPORTE_MOVIMIENTO)
2. If you want to calculate the Accumulated Amount, give the expression as this:
RangeSum(above(SUM(IMPORTE_MOVIMIENTO),0,RowNo() ))
Hope this will help.
Regards,
Nilesh Gangurde

Re: Problems with calculated expressions in a table
carmenhm May 13, 2012 11:07 AM (in response to Nilesh Gangurde )Nilesh,
As I put in my last post, I defined amount as SUM ( {$}IMPORTE_MOVIMIENTO),0,RowNo() )) and it doesn’t work.
I don’t know why it doesn’t work.
Thanks in advance. I’m really pressed by time.

Problems with calculated expressions in a table
Nilesh Gangurde May 13, 2012 12:34 PM (in response to carmenhm)Carmen,
The expression SUM ( {$}IMPORTE_MOVIMIENTO),0,RowNo() )) will not work . because we cant put anything outside the sum function and here you are doing that.
so instead of the above expression Put sum(IMPORTE_MOVIMIENTO) you will get the results as you want. and please refer my last post.
Regards,
Nilesh Gangurde

Re: Problems with calculated expressions in a table
carmenhm May 13, 2012 12:52 PM (in response to Nilesh Gangurde )Nilesh,
But I have to restrict IMPORTE_MOVIMIENTO to a time frame. It’s because of that tat I put SUM ( {$}IMPORTE_MOVIMIENTO).
Is there any way to get the result with the restriction?
Thanks in advance

Problems with calculated expressions in a table
Nilesh Gangurde May 14, 2012 1:53 AM (in response to carmenhm)If you want the results with restrictions then put the table as which i suggested you in the above post.
And also put the list box for the date and then put the slections in the list box and you will get the results as per your selections.
OR
if You want to see the results for the perticular date in the hardcoded form then you can use the following script in the expression.
sum({<DateField = {"1/1/2012"}>} IMPORTE_MOVIMIENTO)
The above expression will give you the data for the 1/1/2012.
Regards,
Nilesh Gangurde

Re: Problems with calculated expressions in a table
carmenhm May 14, 2012 3:19 PM (in response to Nilesh Gangurde )It’s because of that that I put amount as SUM ( {$}IMPORTE_MOVIMIENTO),0,RowNo() ))
It must be a problem of communication, not being English my mother language. I send you a snapshot of the results, being Importe Amount and Importe Acumulado Accumulated Amount.
cid:image001.png@01CD3217.19AE91C0
Thanks in advance

image001.png 147.9 K











