# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join this live chat April 6, 10AM EST - QlikView to Qlik Sense REGISTER
cancel
Showing results for
Did you mean:
Partner

## Formula Assistance

Hi There,

I'm working with data that appears as below to determine the monthly return on investment.

Client:Month:Net Opportunity:
Client A2017-07\$ 18,000
Client B2017-07\$ 12,500
Client C2017-07\$ 20,000
Client A2017-06\$ 25,000
Client B2017-06\$15,000
Client C2017-06

\$ 32,000

In a separate file I have the monthly goals for each client linked by client name.

Client A: \$15,000

Client B: \$ 10,000

Client C: \$18,000

I'm trying to get the following calculation: Net Opportunity / Goal

However, if I have a years worth of data in the spreadsheet I need it to take the goal and multiply by the number of month of data?

So the formula would be Sum of Net Opportunity / (Goal * active months), otherwise its just dividing it by one month goal not the total number of months * goal.

I'm not sure how to accomplish this in qlik. Any suggestions?

Thank you!

1 Solution

Accepted Solutions
MVP

May be this

Sum([Net Opportunity])/(Goal * Count(DISTINCT Month))

4 Replies
MVP

May be this

Sum([Net Opportunity])/(Goal * Count(DISTINCT Month))

Partner
Author

thank you! That worked!

Partner
Author

Hi Sunny:

After looking through this it works well if I select one client at a time but for the KPI field in Qlik Sense I would like to know all clients and because I don't have a specific client selected its coming back with a -.

Any suggestions?

MVP

‌Try this for KPI

Sum(Aggr(Sum([Net Opportunity])/(Goal * Count(DISTINCT Month)), Client))