topic Re: Formula Assistance in QlikView

Formula Assistance

megan_deboer — Wed, 09 Aug 2017 19:04:50 GMT

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!

Re: Formula Assistance

sunny_talwar — Wed, 09 Aug 2017 19:30:05 GMT

May be this

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

Re: Formula Assistance

megan_deboer — Wed, 09 Aug 2017 19:44:25 GMT

thank you! That worked!

Re: Formula Assistance

megan_deboer — Wed, 09 Aug 2017 20:02:06 GMT

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?

Re: Formula Assistance

sunny_talwar — Thu, 10 Aug 2017 00:12:51 GMT

‌Try this for KPI

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