How do you summ one column based on the value of another and then convert that sum into a percentage?

[pnn44794]

Hello:

I need to sum one column based on the value of another column and then convert that sum into a percentage of another number.  Manually, I know how to do it, but I'm not certain about the Qlikview syntax.

[#2]

Hi Perry,

How are you going to implement this?

You have list box to select in "Save Initiative Category" and display percentage in a text box on selecting value.

or

You need everything in a tabular column?

[#3]

If You have listbox to select then you can simply write following in a text box.

Sum( Finance)/Sum({1} Finance) *100 & '%'

by default it shows 100. And if you select Server\Storage, it shows 3.66%

[pnn44794]

Hello Ashok:

My fault.  I should have explained what I was trying to create.    The data is in a loaded straight table and I'm trying to create a gauge that will, hopefully, show the percentage of the overall goal which is the $28.9mm number.  So the gauge, hopefully, goes from red to green and even better dark green indicating the goal has been over achieved, i.e. by 10% or 20%, etc.  Make sense?

[Anil_Babu_Samineni]

You've 6 Server/Storages. In that H Column showing only 3 Values. Does you need only 3 values or you just showing the example. Can you provide the excel to work on it

[pnn44794]

Hello Anil:

I'm just showing an example.  I would need 3 or 500 rows summed up, whatever the count would be for if =Server\Storage, then sum Finance Confirmed Savings and then what's that percentage of $28MM.

I can provide more data tomorrow if needed.  Thanks for your response.

[pnn44794]

Sorry for the delay Anil.

[pnn44794]

Hello Ashok:

My apologies for the delay in replying.  This calculation will be used in a Gauge Chart.  The idea is as the year progresses and the sum amount increases the percent of the goal will increase.  As mentioned above, the goal is $28.9MM.  With the example I gave, 1,058,186 is 3.66% of the goal.  Let's say next month, the total sum amount is $3.6MM.  Now the percent should be 12.46% of the goal of $28.9MM.  Make sense?

[maxgro · Accepted solution]

maybe

sum({$ <[Save Initiative  Category]={Server\Storage}>} [Finance Confirmed 2016 Savings])

/

28900000

[pnn44794]

This works perfect!  Thank you.  Eventually I'll get the syntax down and won't have to ask these questions.