Discussion Board for collaboration related to QlikView App Development.
I'm working with data that appears as below to determine the monthly return on investment.
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?
May be this
Sum([Net Opportunity])/(Goal * Count(DISTINCT Month))
View solution in original post
thank you! That worked!
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 -.
Try this for KPI
Sum(Aggr(Sum([Net Opportunity])/(Goal * Count(DISTINCT Month)), Client))