topic calculation problem in QlikView

calculation problem

Mon, 31 Oct 2011 17:34:39 GMT

Hello Experts,

Please take a look at my qvw and let me know how I can have the calculation (the third line) under the right column.

Thanks

Re: calculation problem

swuehl — Mon, 31 Oct 2011 18:17:50 GMT

Well there are probably multiple ways to achieve what I think you want, what about attached?

I removed the script snippet that I think should calculate the Sales - Forecast figures and added

=if(dimensionality()=0,sum({<TYPE= {Sales}>} DD)-sum({<TYPE= {Forecast}>} DD),Sum(DD))

as expression (similar for the second), so I am using a subtotal line to display the difference.

Hope this helps,

Stefan

calculation problem

Mon, 31 Oct 2011 18:29:34 GMT

Thanks for replying,

The problem is that I would have more calculation line , like: Sales * Forecast and Sales / Forecast and so on.

I don't know how I can handle them with the expression.That's why I did it in the script.

Thanks,

calculation problem

swuehl — Mon, 31 Oct 2011 19:14:13 GMT

I see, maybe try something like this:

Table:

LOAD * FROM

[.\1234.xls]

(biff, embedded labels, table is Sheet1$);

CONCATENATE (Table) LOAD

DATE,

'Sales - Forecast' as TYPE,

sum(if(TYPE='Sales', DD, - DD)) as DD,

sum(if(TYPE='Sales', LBS, -LBS)) as LBS

RESIDENT Table group by DATE;

//WHERE match(TYPE,'Sales','Forecast');

calculation problem

Mon, 31 Oct 2011 19:29:25 GMT

Hello Swuehl,

That was good for one calculation. When I added the second calculation like "Sales + Forecast" it gives you the total of all the rows including the "Sales - Forecast".

A little confusing

Thanks for helping.

Nima

calculation problem

swuehl — Mon, 31 Oct 2011 20:21:02 GMT

Well, another measure should not be a big problem, have you tried like this:

Table:

LOAD * FROM

[.\1234.xls]

(biff, embedded labels, table is Sheet1$);

CONCATENATE (Table) LOAD

DATE,

'Sales - Forecast' as TYPE,

sum(if(TYPE='Sales', DD, - DD)) as DD,

sum(if(TYPE='Sales', LBS, -LBS)) as LBS

RESIDENT Table WHERE match(TYPE,'Sales','Forecast') group by DATE;

CONCATENATE (Table) LOAD

DATE,

'Sales / Forecast %' as TYPE,

100*(sum(if(TYPE='Sales', DD)) / sum(if(TYPE='Forecast',DD))) as DD,

100*(sum(if(TYPE='Sales', LBS)) / sum(if(TYPE='Forecast',LBS))) as LBS

RESIDENT Table WHERE match(TYPE,'Sales','Forecast') group by DATE;

calculation problem

Mon, 31 Oct 2011 20:29:44 GMT

Thanks a lot Swuehl,

That was a great help.

Cheers,

Nima