topic Re: Sum in Expression : For Pivot Table in QlikView

Sum in Expression : For Pivot Table

Fri, 24 May 2013 14:11:37 GMT

Hi,

I have a pivot table which looks like

City Zipcodes NumberOfRestaurant Expression

111111 250 550

Rockford 111111 300 550

111113 400 600

111113 200 600

111111 100 100

indiana 111112 200 200

I am looking to get the sum of each city and the zipcodes in the city.

So the answer should be as shown in the expression above

Under Expresson I tried

Sum(Total<City, Zipcodes>, NumberofRestaurant)

Any suggestions pls.

Thanks

Re: Sum in Expression : For Pivot Table

Gysbert_Wassenaar — Fri, 24 May 2013 15:36:52 GMT

Try Sum(Total<City, Zipcodes> NumberofRestaurant). See attached example.

Re: Sum in Expression : For Pivot Table

Fri, 24 May 2013 19:29:26 GMT

Thanks

This for some reason is not working for me.

Gives me a different number.

My actual expression syntax looks like

if(City like 'Indiana*', sum(total<city,Zipcodes>Numberofrestaurant))

I am not sure why.

Re: Sum in Expression : For Pivot Table

Fri, 24 May 2013 20:20:49 GMT

Thanks for replying.

Still I see the same wrong numbers.

All this formula looks right and the attached qvw also looks good.

Not sure why I am getting the wrong numbers.

Re: Sum in Expression : For Pivot Table

er_mohit — Fri, 24 May 2013 20:25:50 GMT

Where you get the wrong result

if you try like this way

if(City like 'Indiana*', sum(total<city,Zipcodes>Numberofrestaurant))

to get this

try

if(City ={'Indiana*'}, Aggr(NODISTINCT Sum(NumberOfRestaurant),Zipcodes,City))

Re: Sum in Expression : For Pivot Table

Fri, 24 May 2013 20:39:17 GMT

This is giving Error in formula.

Re: Sum in Expression : For Pivot Table

Fri, 24 May 2013 20:55:00 GMT

Thanks for your reply.

This still gives the same wrong numbers.

Re: Sum in Expression : For Pivot Table

Fri, 24 May 2013 21:26:01 GMT

I absolutely understand that the formula are right.

This is what is happening with me.

I have data which is loaded from the qvd file.

Here I am creating a pivot table and applying the formula in the expression . And the numbers coming out are WRONG.

But If I export this data to excel and load this data into new qvw and reapply the same formula in the expression it works like a charm.

So I know that the formula is right.

This I dont understand is how come for the same pivot tht I am doing froma QVD file why are the numbers coming wrong.

Has anyone seen issues like this ?

Re: Sum in Expression : For Pivot Table

er_mohit — Sat, 25 May 2013 10:26:08 GMT

See the attached file not sure why your data comes different when you load it from qvd

but in my File it comes true

See the attached file

make sure that in sort tab your all data is in ascending order

Re: Sum in Expression : For Pivot Table

Sun, 26 May 2013 13:39:53 GMT

Thank you.

I see your file and yes it works as expected.

But same formula on my qvw does not work.

It still gives me wrong numbers.

It seems to be giving me numbers which are lot bigger than what it should be.

I have read lot of article cant figure out why.

There is some calculations which are getting multiplied or .

I will keep digging there has to be some explanation for what I am seeing.

Thanks

Re: Sum in Expression : For Pivot Table

Tue, 28 May 2013 14:05:32 GMT

I think the issue could be because in my data from QVD I have the Zipcodes which are showing as "-".

this I think is giving the wrong numbers.

any suggestion in the formula given on this dicussion how can I exclude the calculations for the "-" ?

Thanks-