calculation problem

[#1]

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

[swuehl]

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

[#3]

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,

[swuehl]

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');

[#5]

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

[swuehl · Accepted solution]

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;

[#7]

Thanks a lot Swuehl,

That was a great help.

Cheers,

Nima