topic Sum Values Only Once in QlikView

Sum Values Only Once

Wed, 26 May 2010 03:23:11 GMT

Dear all,

In advance, thanks for all the comments that you may provide. Here is my situation.

I have this set of values as can be seen in attachment named Foto1

As you can see, for a single brick the values get exactly repeated by each territory calling them. What I'm trying to do is to only sum then once, because if I sum then directly then the values might get duplicated. I've been working around with sum and aggr but no good results came from that.

Guys, any ideas?

Again thanks for your help.

Regards,

Andrés A. Jején P.

Sum Values Only Once

Wed, 26 May 2010 06:57:20 GMT

u will do sum() group by treatry id

Sum Values Only Once

Wed, 26 May 2010 08:00:08 GMT

Not that I would know - but are you sure that this is the correct data you have to work with and duplication is not itself the issue?

I all your territories use the same bricks you can conditionaly sum on one of the territories,

vT = min(territory)

sum (if (territory = vT, [Sold Units]))

Regards

Juerg

Sum Values Only Once

Wed, 26 May 2010 14:39:00 GMT

Hi Juerg,

In fact I'm positive that the data is correct, many territories may call the same brick (sales territory). What I'm trying to do is to extract a national total, by sum the brick value only once no matter if it is called by only one or by several territories.

Thanks for your answer!

Sum Values Only Once

Wed, 26 May 2010 14:39:42 GMT

Hi Ashish,

In fact, What I'm trying to do is to perform the sum but avoiding to take into account the territory_id.

Thanks!

Sum Values Only Once

Wed, 26 May 2010 14:43:26 GMT

Have you tried Sum(DISTINCT Sold_Units)?

Sum Values Only Once

Wed, 26 May 2010 15:35:16 GMT

Hi Aaron,

Yes, but it will sum the distinct numeric values available in the table, and that's not what I want to do. What I want is to sum the values for all the distinct bricks only once, regardless that is being seen in more than one territory.

Thanks for your answer!

Sum Values Only Once

Thu, 27 May 2010 14:10:32 GMT

Hi Andres

still not sure I understood your expected result. Would a distinct list of brick_id, product_id and [Sold Units] give you this result?

Juerg

Sum Values Only Once

Thu, 27 May 2010 14:57:54 GMT

Hi Juerg,

Yes, that is the result that I need, but I can't accomplish that on the data level since I have another analysis that will use the territory level for its purposes. What I need is to analyse this data set only by brick and to sum the values only once.

Thanks a lot for your answer.

Sum Values Only Once

Thu, 27 May 2010 15:10:52 GMT

In the example at the beginning, are you wanting the sum of the Sold_Units to appear only once at the bottom or do you want it repeated for each territory?

Sum Values Only Once

Thu, 27 May 2010 15:18:10 GMT

Hi Anders

But you could create a new table giving you these brick/product related values ignoring the territory. You can still use your "full" table for other diagrams.

Regards

Jürg