topic Re: Re: wrong multiplication result in straight tbl in QlikView

wrong multiplication result in straight tbl

surajap123 — Mon, 12 May 2014 10:33:52 GMT

Hi All,

I have muliplied 2 fields to get final value(TotalHours). When i see the preview in the table viewer, the TotalHours value is correct.

But in the straight table when i did sum, i get wrong result.

Please tell me where i am dong wrong.

Re: wrong multiplication result in straight tbl

its_anandrjs — Mon, 12 May 2014 10:39:11 GMT

Try to get aggregate in the load script like

Temp:

Load

CourseName,

units,

hours,

(units * hours) as TotalHours

From

[hours multiplication issue.xls]

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

Load

CourseName,

units,

hours,

Sum(units * hours) as TotalHours

Resident Temp

Group by

CourseName,

units,

hours;

Drop table Temp:

Re: wrong multiplication result in straight tbl

surajap123 — Mon, 12 May 2014 11:13:42 GMT

Hi Anand,

Thanks for your reply.

I tried working as you suggested, but its not working.

The issue seems to be the expression used in the straight table.

Here, i am attaching the source and the modified app. Please help !!

Re: Re: wrong multiplication result in straight tbl

anbu1984 — Mon, 12 May 2014 11:30:05 GMT

Add expression sum(U)* sum(Hrs) for Total

Re: Re: wrong multiplication result in straight tbl

anbu1984 — Mon, 12 May 2014 11:31:32 GMT

You get wrong result because

U,Hrs

2,1 --> 2*1=2

1,0 --> 1*0=0

So your total was 2+0=2

Re: wrong multiplication result in straight tbl

surajap123 — Mon, 12 May 2014 11:52:54 GMT

Hi Anbu,

Thanks a lot..its working.

I am using set analyis in that expression, so do i need to change the expression from -

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}Total)

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}Hrs) *

SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}U)

Sorry to bother you..but out of curiosity i want to know-

As you can see in the script, i have calculated the 'Total' field as Sum(units * hours), which is giving right result in table viewer.

But in straight table the 'Total' field value is incorrect. Why i need to write expression sum(U)* sum(Hrs)?

Re: wrong multiplication result in straight tbl

anbu1984 — Mon, 12 May 2014 12:11:43 GMT

Your total is not correct in script

Sum(Total) is 2+0. So use sum(U) * sum(Hrs)

Re: wrong multiplication result in straight tbl

anbu1984 — Mon, 12 May 2014 12:15:37 GMT

Yes use the expr

=SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}Hrs) *

SUM({<Year={'$(vMaxYear)'},Date={'<=$(vMaxDate)'},Month=,Day=,MonthYear=>}U)