Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
maybe
sum({$ <[Save Initiative Category]={Server\Storage}>} [Finance Confirmed 2016 Savings])
/
28900000
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?
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%
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?
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
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.
Sorry for the delay Anil.
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?
maybe
sum({$ <[Save Initiative Category]={Server\Storage}>} [Finance Confirmed 2016 Savings])
/
28900000
This works perfect! Thank you. Eventually I'll get the syntax down and won't have to ask these questions.