topic Re: Split same value into the sum in QlikView

Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 15:51:02 GMT

Hello,

I have many salesman,

How could sum the same salesman in 2 groups separately.

If(Vendor1='a','a',

If(Vendor1='a','b',

If(Vendor2='b','b',

If(Vendor3='c','b',

If(Vendor4='d','b',

When I do the statement vendor 1 dissapear

I want that my pivot show

The total in b

and the total a

Final Result

158.539.379

Thanks!

Re: Split same value into the sum

sunny_talwar — Fri, 06 Feb 2015 16:27:33 GMT

The field names in your QlikView file are different then the example you are giving here. I have no idea what matches to what.

Best,

Re: Split same value into the sum

cesaraccardi — Fri, 06 Feb 2015 16:31:46 GMT

Hi Pablo,

Have a look at the attachment.

If I understand it correctly you have a many to many relationship between salesman and group. So you have to create a separate table for this to work. I have included some code at the bottom of the script, you will have to reload it to see the results.

Kind Regards,

Cesar

Re: Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 16:43:40 GMT

Hi,

Thanks for your reply,

For January 2014 I have in my excel file

Final Result

Blue has 58.054.886

Red has 100.484.493

Total

158.539.379

You have 277.139.309

Re: Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 16:45:12 GMT

That was an example. is not my script.

Re: Split same value into the sum

cesaraccardi — Fri, 06 Feb 2015 16:50:00 GMT

Hi,

Can you post a screenshot of the application after it was reloaded please?

Re: Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 16:59:41 GMT

After reload,

Blue its Ok 58.054.886

Red Should be 100.484.493

Re: Split same value into the sum

cesaraccardi — Fri, 06 Feb 2015 17:04:38 GMT

Pablo,

Alright, try replacing the two LOAD statements at the bottom by this one, that should give what you are looking for.

Ingresos:

LOAD * INLINE
[
vendedor,Ingresos
NM,Blue
NM,Red
JEC,Red
MT,Red
PR,Red
];

Re: Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 17:13:48 GMT

Gave the same result, Check out that cod NM = Blue appears in Red Too.

So with my first app i had this:

What I Want is

Red 42.429.607 + 58.054.886

Blue 58.054.886

Final Total 158.539.379

Re: Split same value into the sum

cesaraccardi — Fri, 06 Feb 2015 17:19:45 GMT

Ok,

If you were using a straight table it would be just a case of changing the total calculation, for the pivot table you will have to change the expression as well:

sum(aggr(sum(monto_actual),ano,mes,Ingresos))

Re: Split same value into the sum

pgalvezt — Fri, 06 Feb 2015 17:23:50 GMT

Gave the same result that pivot table...