Qlik Community

Ask a Question

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 
Search instead for 
Did you mean: 
megan_deboer
Partner
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
sunny_talwar

May be this

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

View solution in original post

4 Replies
sunny_talwar

May be this

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

View solution in original post

megan_deboer
Partner
Partner
Author

thank you! That worked!

megan_deboer
Partner
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?

sunny_talwar

‌Try this for KPI

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