topic Problems with calculated expressions in a table in QlikView

Problems with calculated expressions in a table

Thu, 10 May 2012 11:15:28 GMT

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

Thank you

Problems with calculated expressions in a table

nilesh_gangurde — Thu, 10 May 2012 11:37:13 GMT

Hi Carmen,

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

Regards,

Nilesh Gangurde

Re: Problems with calculated expressions in a table

Thu, 10 May 2012 11:40:11 GMT

Hi,

Can you eloberate your requirement.

Regards

Ravikumar

Re: Problems with calculated expressions in a table

Thu, 10 May 2012 11:56:54 GMT

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

Problems with calculated expressions in a table

nilesh_gangurde — Thu, 10 May 2012 12:07:49 GMT

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

Thu, 10 May 2012 14:50:53 GMT

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.

Re: Problems with calculated expressions in a table

nilesh_gangurde — Fri, 11 May 2012 07:04:16 GMT

Hi Carmen,

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

Regards,

Nilesh Gangurde

Re: Problems with calculated expressions in a table

Fri, 11 May 2012 12:12:59 GMT

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 — Sat, 12 May 2012 05:26:48 GMT

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

Sun, 13 May 2012 15:07:54 GMT

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 — Sun, 13 May 2012 16:34:29 GMT

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

Sun, 13 May 2012 16:52:26 GMT

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 — Mon, 14 May 2012 05:53:25 GMT

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.

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

Mon, 14 May 2012 19:19:11 GMT

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.

Thanks in advance