topic Re: Set analysis Aggr in QlikView

Set analysis Aggr

pgalvezt — Mon, 03 Feb 2014 19:43:24 GMT

Hello,

Can you help me with this syntaxis:

Aggr(sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor,Familia) Thrown null.

Thanks!

Re: Set analysis Aggr

Clever_Anjos — Mon, 03 Feb 2014 19:46:41 GMT

You need another aggregation function depeding what you want

max(Aggr(sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor,Familia) )

avg(Aggr(sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor,Familia) )

Re: Set analysis Aggr

Gysbert_Wassenaar — Mon, 03 Feb 2014 20:13:55 GMT

Is Valor_Factor a field name or a variable? If it's a field you can get nulls if a value from the dimension Familia has several different Valor-Factor values. Perhaps you need something like Aggr(sum({<Cuenta={'31010101'}>}Monto*-1 /Valor_Factor),Familia). And as cleveranjos says you may have to add an aggregation function like max, avg or sum around your current expression.

Re: Re: Set analysis Aggr

pgalvezt — Tue, 04 Feb 2014 15:01:49 GMT

Hello,

Thank you for your answers,

Still give Null. I've tried this because QlikView Show a different sum when export to excel. I put January like Example

Can you help me with this?

Thanks!

Re: Re: Re: Set analysis Aggr

Gysbert_Wassenaar — Tue, 04 Feb 2014 15:18:07 GMT

See attached qvw.

Re: Set analysis Aggr

pgalvezt — Tue, 04 Feb 2014 15:28:36 GMT

Hi,

Gysbert I had already that formula.What I want is to get the first result (358..).

Thanks!

Re: Set analysis Aggr

Gysbert_Wassenaar — Tue, 04 Feb 2014 15:30:35 GMT

In that case, please do explain what you want. Posting a document that contains the result you say you want is confusing. If you already have the result then why post the question?

If you want the value 358961245 displayed in a text box then get rid of the aggr: sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor

Re: Set analysis Aggr

pgalvezt — Tue, 04 Feb 2014 15:37:30 GMT

Hi,

Sorry maybe I explained very bad the case Sorry about that

My formula sum({<Cuenta={'31010101'}>}Monto*-1) /Valor_Factor Works fine with many views and sheets. But with some graphs does´t work the real result is 358961245. QlikView show that result (But When I export Excel the sume Gives 721677934. What I want is the real sum in excel 358961245. I hope you can understand the case...

Thanks!

Re: Set analysis Aggr

Gysbert_Wassenaar — Tue, 04 Feb 2014 15:58:21 GMT

Well, then you need to buy a new Excel . Yours is broken. If I export your table to Excel the subtotal 358961245 is exported to excel too. If I manually add a sum formula in excel to sum the values of the rows I get 721677934 of course, because those values that are summed do in fact sum up to a total of 721677934

Re: Set analysis Aggr

pgalvezt — Tue, 04 Feb 2014 17:34:51 GMT

Hi,

Are you sure??? I did the sum with a calculator (Watching QlikView) And get 721677934. I Think I left to add an aggregation to my formula until get 358961245 (Dimenssions). But I Still I can not get the real result. With your formula QlikView Shows 721677934. I' ve been moving the dimension inside the formula. If I change the dimension to vendor (Vendedor) Works. But by family (Familia) I don´t know why thrown another result.

Thanks!

Re: Set analysis Aggr

pgalvezt — Tue, 04 Feb 2014 18:01:06 GMT

Hi,

I got it!!

Sum(Aggr(sum({<Cuenta={'31010101'}>}Monto*-1),Vendedor,Tipo_Cliente,Factura)) Now in Excel Sum 358... I gave you a star for your help.

Thank you very much!