topic Re: Multiple field sum in QlikView

Multiple field sum

manideep78 — Thu, 27 Aug 2015 07:23:49 GMT

Dear Community,

I have field called Item. This Item is an Island

Again Each Item is a Separate field.

Ex:

If I select Item Ball, I need Sum(Ball) =30

If I select Item Pen, I need Sum(Pen) = 70

If I select both Ball and Pen, I need Sum(Ball)+ Sum(Pen) = 100 or Sum(Ball+Pen) =100

like wise if I select all the items, I need sum of all the items to be added.

How can I achieve this?

Thanks in advance.

Regards

Manideep

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 07:35:52 GMT

Try it with this concept may be:

Expression:

=Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 1))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 1)))) +

Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 2))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 2))))

Output when nothing is selected:

When Pen is selected:

When both are selected:

Attaching the qvw for reference.

HTH

Best,

Sunny

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 07:38:15 GMT

I performed the above for just two items, but it can be expanded for all the Items in your list:

Expression:

=Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 1))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 1)))) +

Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 2))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 2)))) +

Sum($(=If(Len(Trim(SubField(Concat(DISTINCT Item, ','), ',', 3))) = 0, 0, SubField(Concat(DISTINCT Item, ','), ',', 3)))) +

and so on....

Re: Multiple field sum

tresB — Thu, 27 Aug 2015 07:38:59 GMT

Like in attached sample?

=Sum($(=GetFieldSelections(Item,'+')))

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 07:42:06 GMT

Hi sunindia‌

Thanks for the quick response.

Instead of writing one expression for 1 item, I would like to make it dynamic, because I don't know how many Items I will have, they will be added dynamically.

So each and every time if the item is added I need to change the expression which is not possible.

Is there any alternative way in getting this?

Regards,

Manideep

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 07:50:02 GMT

I would look at what tresesco‌ has proposed. The only issue I saw was when you select both Ball and Pen it gave me a sum of 200 instead of 100, but I cannot open his attached qvw in which he might have fixed the issue.

For not knowing how many items there could be, you can always create 200 of the above expressions, because it will default to 0 when there are no item

But I guess we can definitely look for a better solution

Best,

Sunny

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 07:54:52 GMT

Hi tresesco‌

This is nearer to my requirement

I tried this, But this not working when my Item contains 2 or 3 spaces. and I should not remove those spaces.

Ex: Compass Box

I am getting an error message.

PFA!!!

Re: Multiple field sum

tresB — Thu, 27 Aug 2015 08:12:40 GMT

=Sum($(='['&GetFieldSelections(Item,']+[')&']'))

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 08:24:36 GMT

Thanks tresesco‌

It worked for me.

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 12:03:52 GMT

Hi tresesco‌

If there is 0 any individual item field, the total sum getting 0. Can you let me know how can I overcome this?

Thanks in advance.

Regards

Manideep

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 12:07:52 GMT

Try this for your 7 Item concern:

=Sum($(='['&GetFieldSelections(Item,']+[', 999999)&']'))

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 12:10:41 GMT

Try this for the 0 concern may be:

=RangeSum($(='Sum(['&GetFieldSelections(Item,']),Sum([', 999999)&'])'))

Re: Multiple field sum

CELAMBARASAN — Thu, 27 Aug 2015 12:17:29 GMT

Try this, it will give you desired result I guess

=$(='RangeSum(' &Concat('Sum([' & Item &'])', ',') & ')')

Hope it helps

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 12:18:36 GMT

Perfect sunindia‌

Thanks

Re: Multiple field sum

manideep78 — Thu, 27 Aug 2015 12:19:35 GMT

Actually, the second one resolved both.

Re: Multiple field sum

sunny_talwar — Thu, 27 Aug 2015 12:21:33 GMT

Awesome

I am glad it worked.

Best,

Sunny

Re: Multiple field sum

manideep78 — Wed, 02 Sep 2015 13:48:55 GMT

Hi sunindia,

Now I want to put this expression in alternate states like

Original Expression:

=RangeSum($(='Sum(['&GetFieldSelections(Item,']),Sum([', 999999)&'])'))

using Alternate states:

=RangeSum($(='Sum({AS_NoSel * $}['&GetFieldSelections(Item,']),Sum([', 999999)&'])'))

I tried the second expression, But not giving the exact result.

What's wrong in the above expression?

Thanks & Regards

Manideep

Re: Multiple field sum

manideep78 — Wed, 02 Sep 2015 14:08:53 GMT

Is there a way we can add the Alternate states in the above expression