Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Problems with calculated expressions in a table

I need to create a table like the one I attach in Excel. How can I do this?

Thank you

13 Replies
nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hi Carmen,

Attach the sample data. and what is the requirement please elaborate.

Regards,

Nilesh Gangurde

Not applicable
Author

Hi,

Can you eloberate your requirement.

Regards

Ravikumar

Not applicable
Author

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

nilesh_gangurde
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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.

nilesh_gangurde
Partner - Specialist
Partner - Specialist

Hi Carmen,

Find the attachement there i caluculated the values which you gave at first post.

Regards,

Nilesh Gangurde

Not applicable
Author

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

nilesh_gangurde
Partner - Specialist
Partner - Specialist

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

Not applicable
Author

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.