Formula Assistance

[megan_deboer]

Hi There,

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

Client:	Month:	Net Opportunity:
Client A	2017-07	$ 18,000
Client B	2017-07	$ 12,500
Client C	2017-07	$ 20,000
Client A	2017-06	$ 25,000
Client B	2017-06	$15,000
Client C	2017-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!

[sunny_talwar · Accepted solution]

May be this

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

[megan_deboer]

thank you! That worked!

[megan_deboer]

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?

[sunny_talwar]

‌Try this for KPI

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