Calculation issue

[#1]

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 ???!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

[swuehl]

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?

[sujeetsingh]

Take set analysis it may go well!!

[m_woolf]

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

[eddysanchez]

The problem is the repetitions of rows in another tables

Try

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

and compare results

[#6]

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 !!!

[#7]

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.

[kaushiknsolanki · Accepted solution]

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

[#9]

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

regards

Bilal El-Abd

[kaushiknsolanki]

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