topic Re: pls help..............simple conditional calculation in QlikView

pls help..............simple conditional calculation

Thu, 11 Jun 2015 10:53:44 GMT

Hi Experts,

i have tables linked like below.

RSM	Branch No	Branch
Mathew	79	BARKING
	134	BOREHAMWOOD
	23	COLLIERS WOOD
	74	EDMONTON
	823	ENFIELD (UA)
	115	HARROW
	24	HORNSEY
Coles	1	WEMBLEY
Adam	54	ALDERSHOT
	112	BASINGSTOKE
	195	CAMBERLEY
	47	CHERTSEY

Branch	Branch No	Working DAYS
WEMBLEY	1	8.5
MANCHESTER	2	8.5
MILTON KEYNES	18	8.5
SHEFFIELD	19	8.5
READING	20	8.5
SWINDON	21	8.5
LEICESTER	22	8.5
COLLIERS WOOD	23	8.5
HORNSEY	24	8.5
NOTTINGHAM	25	8.5
BURY ST. EDMUNDS	26	8.5
EDINBURGH	27	8.5
HAYES	28	8.5

i have to calculate the working days for RSM.

when i calculated, i m getting the result as below.

RSM Name	Working DAYS
Mathew	127.5
Coles	8.5
Adam	119

this is not the requirement.

i should get the result as

RSM Name	Working DAYS
Mathew	8.5
Coles	8.5
Adam	8.5

the result highlighted in blue is the correct one i should get.

how can i do this in the expression.

pls help................

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 10:57:30 GMT

The first table and the second table don't have all the matching Branch and Branch No. How do you know Mattew only has 8.5 working days when there is no matching Branch and Branch No. in the second table?? Just trying to understand the logic here.

Best,

Sunny

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 10:58:08 GMT

and if it is always going to be 8.5 why not just create a chart with RSM as Dimension and 8.5 as your expression?

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:03:25 GMT

hi,

actually this s a sample data.not exact

each RSM has around 10 branches.

but our working days table is set to branch.(it differs by branch)

for ex. this month till date, we got 8.5 working days.

lets say branch 1 has got 8.5 working days till now,branch 2 has got 8 working days till now,

branch 3 has got 9 working days till now and so on.

i need to calculate the working days for RSM.

How can i do that in pivot table.

pls help.

looking forward to your reply.

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:05:33 GMT

hi sunindia,

i was trying to share my posts two days back with you. but getting error msg as i m not connected with u.

pls give a follow up to me.

so that it ll be helpful to me plssssss

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 11:06:31 GMT

If each RSM has 10 branches and you are trying to do a Sum of working days then you will essentially be summing them the working Days from each of the 10 branches. Do you not want that? May be you want to use an average instead to get what you are looking for. Have you tried average???

Best,

Sunny

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 11:07:32 GMT

Not sure which post you are talking about???

Re: pls help..............simple conditional calculation

buzzy996 — Thu, 11 Jun 2015 11:11:05 GMT

first u can tell to us,how u got ........

Adam

8.5?

as per the above data u wont get that figure,any how!

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:13:29 GMT

i would like to hav solution for this current post only.

but personally,i would like to ask help from you whenever i get any doubt.

i was trying to send msg at that time but couldnt connect with u. i think the reason is i m following you, but you are not following me.

pls help

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:15:06 GMT

again, its just a sample data.

some branches have working days 8, some have 8.5, some have 9 till now.

but i need to calculate for RSM, not for branch.

pls help

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 11:15:18 GMT

Just followed you

I don't understand why both of us need to follow each other to message, but I guess that's how it works.

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:16:22 GMT

i tried avg, but showing 0.85

i should get 8.5.

i tried to change number format, tried with Num(),...

but nothing works out.

help plssssss

Re: pls help..............simple conditional calculation

buzzy996 — Thu, 11 Jun 2015 11:17:39 GMT

sorry,by the time i posted my comment i was not able to see the previous discussion.

i think u have to do some data cleaning/profiling here.

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:17:50 GMT

thanks for following

Re: pls help..............simple conditional calculation

sunny_talwar — Thu, 11 Jun 2015 11:18:20 GMT

I guess is it possible to share a sample? Not easy to say what it could be without looking at it.

Re: pls help..............simple conditional calculation

Thu, 11 Jun 2015 11:19:33 GMT

not getting you.

pls be more clear

Re: pls help..............simple conditional calculation

buzzy996 — Thu, 11 Jun 2015 11:23:42 GMT

u have to share/provide here proper dataset first..i think ur copied some data from some source..some other data from different source.

due to tht,it's difficult to suggest expected solution to u.

pls share if possible ur dataset completely or some meaningful data.