topic Re: Calculation issue in QlikView

Calculation issue

Sun, 07 Apr 2013 16:24:50 GMT

I have the attached pivot table. For the dimension i used locations.

now for morning shift calculation i used = SUM(IF(numTime <= AMEnd and numTime >= AMStart,1,0)) But it gives wrong result

But if i substitute the values for AMEnd and AMStart that is for example Bateen: = SUM(IF(numTime <= 55800 and numTime >= 32400,1,0)) then i get the correct result.

WHAT AM I DOING WRONG HERE ???!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Re: Calculation issue

swuehl — Sun, 07 Apr 2013 23:41:46 GMT

It's quite hard to tell what's wrong without knowing anything more detailed about your data model, i.e. how the fields are related, and what their value type and range is. Also, please tell us what your expected result is (instead of just stating that it's wrong).

Could you maybe post a small sample app that demonstrate your issue?

Re: Calculation issue

sujeetsingh — Mon, 08 Apr 2013 06:05:12 GMT

Take set analysis it may go well!!

Re: Calculation issue

m_woolf — Mon, 08 Apr 2013 11:10:06 GMT

Your post suggests that you need num(AMEnd) and num(AMStart)

Re: Calculation issue

eddysanchez — Mon, 08 Apr 2013 17:56:20 GMT

The problem is the repetitions of rows in another tables

Try

SUM(distinct IF(numTime <= AMEnd and numTime >=AMStart,1,0))

and compare results

Re: Calculation issue

Tue, 09 Apr 2013 05:04:30 GMT

Note that in the table i attached there is 2 columns AMShift and PMShift to show if the formula uses those values for calculation and it does, but the result i get (surveys done in AM shift) is more than the number of surveys done at that location. so when i substitute in formula with the original value it works, so there is something about AMShift and PMShift which is wrong !!!

Re: Calculation issue

Mon, 15 Apr 2013 17:28:48 GMT

This is taking long and no solution till now !!! For the morning shift i put numbers so it work, but what if i use AMStart and AMEnd. Note that each location have its own AMStart and AMEnd. I also attached the data model for reference.

Re: Calculation issue

kaushiknsolanki — Mon, 15 Apr 2013 18:16:10 GMT

Hi,

Use below expression.

=sum(aggr( IF(numTime >= AMStart and numTime <= AMEnd ,1,0),numTime,Locations))

Have a look at the attached example.

Regards,

Kaushik Solanki

Re: Calculation issue

Mon, 15 Apr 2013 21:30:19 GMT

Great thanks dude. Could you just explain what was wrong and how does this code solve the problem?

regards

Bilal El-Abd

Re: Calculation issue

kaushiknsolanki — Tue, 16 Apr 2013 06:01:48 GMT

Hi,

The thing is there was so many numValue for each of the locations, thus it was necessory to calculate the expression for each values, and thus i have used aggr function at location and numValue level.

Regards,

Kaushik Solanki