We have purchased Qlik Sense and (after watching many training videos) are getting our feet wet with attempting to build an App.
For the current month, the App is to show a table with sums of the different transaction types (this is the easy part). For example, for July 2017, display the sum of funded loans, sum of received payments, sum of charge offs, etc.
However, we also want to show a column regarding how we are doing as compared to this month's goal per transactions type. This is the 'target' column below.
For example,
| Transaction type | Month | Target | Actual |
|---|
| Fundings | Jul-2017 | $1,000,000 | $400,000 |
| Payments | Jul-2017 | $500,000 | $230,000 |
We are using the Data Load Editor to bring in two disparate data sets: one query that pulls all-time transactions and another query that pulls in the company's monthly goals. These two data sets share no common fields.
We are using a pivot table in Qlik Sense with the valuelist('Fundings','Payments') for the Rows. Then, we simply do a sum of all fundings to get the 'Actual' column values.
However, how do we cherry-pick the goal ('Target') for Fundings from the Goals data set?
Thoughts:
1) Do we need to be able to bring in the goals in the transactions data set? E.g. in the transactions query, we could add variables:
E.g. Goal_Fundings = (SELECT TOP 1 Value FROM [Goals] WHERE GoalTypeID = 1 AND [Date] = CAST(DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) AS Date))
The bad thing is that this will create an extra column in the transactions table pull that will basically repeat this month's Goal for fundings. Since the transactions data set is 20M rows, that one goal for this month's fundings will be repeated 20M times when in reality, we just need it once to display in this table.
2) Is there a way in Qlik Sense; either through a master item or through an expression in the data load editor (or another way?); to keep the two data sets separated and simply create a variable that picks this month's fundings goal from that data set?
Thanks!
