Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

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
Valued Contributor

Problems with calculated expressions in a table

Hi Carmen,

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

Regards,

Nilesh Gangurde

Not applicable

Re: Problems with calculated expressions in a table

Hi,

Can you eloberate your requirement.

Regards

Ravikumar

Not applicable

Re: Problems with calculated expressions in a table

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
Valued Contributor

Problems with calculated expressions in a table

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

Re: Problems with calculated expressions in a table

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
Valued Contributor

Re: Problems with calculated expressions in a table

Hi Carmen,

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

Regards,

Nilesh Gangurde

Not applicable

Re: Problems with calculated expressions in a table

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
Valued Contributor

Problems with calculated expressions in a table

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

Re: Problems with calculated expressions in a table

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.

Community Browser