topic Re: Data = 0 in QlikView

Data = 0

pgalvezt — Thu, 14 May 2015 19:41:11 GMT

Hello,

I have a table with many departments and values by Accounts and Cc. I need to appears the other values. When I do clic on 31020301 QlikView Shows 0. Same thing with 31020302. For 31020303 the formula works fine. But I need the other values.

Thanks!!

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 19:49:36 GMT

The qvw is asking for User Identification

Please give the User ID or re-add another version of qvw.

Best,

Sunny

Re: Data = 0

pgalvezt — Thu, 14 May 2015 19:57:57 GMT

Sorry,

Now goes without User Identification

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 20:02:46 GMT

You have specified it in your expression that only when Cuenta is 31020303, display a result. Why did you add it there?

(Sum(Aggr(If(Cuenta='31020303' and Cc='4407',Sum({<Año={'2015'}>}MONTO*-1)*0.7513, Sum({<Año={'2015'}>}MONTO*-1)),Cc)))-

(Sum(Aggr(If(Cuenta='31020303' and Cc='4400',Sum({<Año={'2015'}>}MONTO*-1)),Cc)))-

(Sum(Aggr(If(Cuenta and Cc,Sum({<Año={'2015'}>}MONTO*-1)),Cc)))

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 20:04:50 GMT

What is the output that you are expecting to see?

Re: Data = 0

pgalvezt — Thu, 14 May 2015 20:15:57 GMT

Is an excel requirement. I have the same account but with 2 different Cc. So the formula in excel is

=(Range in excel) - 'Sheet'!File*0,7513)*-1

everything else must be the same values that in excel.

Re: Data = 0

pgalvezt — Thu, 14 May 2015 20:18:18 GMT

Sum({<Año={'2015'}>}MONTO*-1)

This formula allow you see all data. Was the initial formula. I have to change it because people ask for a new requeriment.

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 20:19:46 GMT

What was the new requirement that made you change the formula?

May be that will help resolve the issue

Best,

Sunny

Re: Data = 0

pgalvezt — Thu, 14 May 2015 20:29:23 GMT

(Sum(Aggr(If(Cuenta='31020303' and Cc='4407',Sum({<Año={'2015'}>}MONTO*-1)*0.7513, Sum({<Año={'2015'}>}MONTO*-1)),Cc)))-

(Sum(Aggr(If(Cuenta='31020303' and Cc='4400',Sum({<Año={'2015'}>}MONTO*-1)),Cc)))-

(Sum(Aggr(If(Cuenta and Cc,Sum({<Año={'2015'}>}MONTO*-1)),Cc)))

The reuirement is that accoount 31020303 and Cc 4407 must be * by 0.7513, then Range - (that accoount 31020303 and Cc 4407 must be * by 0.7513)

Range means Sum({<Año={'2015'}>}MONTO*-1)

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 20:30:38 GMT

Is this the goal:

If(Cuenta='31020303' and Cc='4407' then do this: Sum({<Año={'2015'}>}MONTO*-1)*0.7513

otherwise do this: Sum({<Año={'2015'}>}MONTO*-1)

Re: Data = 0

pgalvezt — Thu, 14 May 2015 20:31:43 GMT

The final result is -3.156.576. Works fine but the other values the other accounts shows 0.

Re: Data = 0

sunny_talwar — Thu, 14 May 2015 20:40:53 GMT

1st of all, I don't see Cuenta = 31020303 in your data:

2nd, try to see if this expression does what you are trying to do:

=If(Cuenta='31020303' and Cc='4407', Sum({<Año={'2015'}, Cuenta = {'31020303'}, Cc = {'4407'}>} MONTO)*-0.7513, Sum({<Año={'2015'}>}MONTO)* -1)

3rd let me know if it still doesn't work

Best,

Sunny

Re: Data = 0

pgalvezt — Thu, 14 May 2015 21:00:24 GMT

Sunindia,

Put this formula:

Sum({<Año={'2015'}>}MONTO*-1)-

((Sum(Aggr(If(Cuenta='31020303' and Cc='4407',Sum({<Año={'2015'}>}MONTO*-1)*0.7513, Sum({<Año={'2015'}>}MONTO*-1)),Cc)))-

(Sum(Aggr(If(Cuenta='31020303' and Cc='4400',Sum({<Año={'2015'}>}MONTO*-1)),Cc))))

Gives 3156576... That is correct... but I need the other values as If I were showing just Sum({<Año={'2015'}>}MONTO*-1)

Wooww So Sorry, I Attached a new document with 31020303 included.

Re: Data = 0

sunny_talwar — Fri, 15 May 2015 09:23:14 GMT

Now this new qvw file is again asking me for a User ID.

Re: Data = 0

pgalvezt — Fri, 15 May 2015 15:20:21 GMT

I have deleted again the hide script. the idea is to show the other accounts. This result that appear is the correct. but when I do clic in another account qlikview shows 0.

Thanks!

Re: Data = 0

pgalvezt — Fri, 15 May 2015 16:46:42 GMT

Look,

Sum(Aggr(If(Cuenta='31020303' and Subcuenta='' and Cc='4407',Sum({<Año={'2015'}>}MONTO*-1)*0.7513

,Sum({<Año={'2015'}>}MONTO*-1)),Cuenta,Subcuenta,Cc))

This result give me:

4400 = -1.275.160

4407 = -5.683.585

What I want is to subtract -8.840.160 to -5.683.585 = 3.156.576 (Final Result)

Sum({<Año={'2015'}>}MONTO*-1) is -8.840.160

Sum({<Año={'2015'}>}MONTO*-1)*0.7513 is -5.683.585

4400 = must be deleted I think.

Re: Data = 0

pgalvezt — Fri, 15 May 2015 18:04:53 GMT

Could you see the new test qvw?

Re: Data = 0

sunny_talwar — Fri, 15 May 2015 18:06:23 GMT

Now it only has just one Cuenta can't see other to test the other scenarios

Re: Data = 0

pgalvezt — Fri, 15 May 2015 18:27:42 GMT

I included the other accounts.

Look,

Sum(Aggr(If(Cuenta='31020303' and Subcuenta='' and Cc='4407',Sum({<Año={'2015'}>}MONTO*-1)*0.7513

,Sum({<Año={'2015'}>}MONTO*-1)),Cuenta,Subcuenta,Cc))

This result give me:

4400 = -1.275.160

4407 = -5.683.585

What I want is to subtract -8.840.160 to -5.683.585 = 3.156.576 (Final Result)

Sum({<Año={'2015'}>}MONTO*-1) is -8.840.160

Sum({<Año={'2015'}>}MONTO*-1)*0.7513 is -5.683.585

4400 = must be deleted I think.

Re: Data = 0

pgalvezt — Fri, 15 May 2015 18:45:09 GMT

Could you see the app?