Hi everyone,
I just started QlikView development recently for our sales team. I having some issue and unable to find a relevant solution.
So here is the details:
| Employee ID | History Rate | Sales Target Q1 | Sales Target Q2 | Sales Target Q3 | Sales Covered Q1 | Sales Covered Q2 | Sales Covered Q3 |
| Emp 1 | 50% | 200000 | 190000 | 150000 | 150000 | 110000 | 55000 |
| Emp 2 | 65% | 350000 | 300000 | 250000 | 280000 | 200000 | 78000 |
| Emp 3 | 60% | 180000 | 100000 | 150000 | 125000 | 80000 | 90000 |
I have created a QlikView straight table as below:
| Employee ID | History Rate | User Provided Rate | Rate (History/User) | Sales Target | Sales Covered |
| Emp 1 | 50% | 70% | 70% | 200000 | 150000 |
| Emp 2 | 65% | 80% | 80% | 350000 | 280000 |
| Emp 3 | 60% | 0% | 60% | 180000 | 125000 |
| Total | | | | 730000 | 555000 |
There is also a multibox in the dashboard that allow user to select 1 record at 1 time:
Selection menu:
- Quarter 1
- Quarter 2
- Quarter 3
The column 'User Provided Rate' is a inputfield. Yes, it allow user to input value to this column. The 'Rate(History/User)' column will display the bigger value between 'History Rate' and 'User Provided Rate'. For example, Emp 1 has the user provided rate higher that the history rate then, this will display 70% in Emp 1 Rate(History/User).
The expression for the 'Rate(History/User)' column is:
if([History Rate] >InputFieldUserRate,[History Rate],InputFieldUserRate)
The InputFieldUserRate has been declared in script as INPUTFIELD.
For the column named 'Sales Target', i added the expression that will detect the selection of the menu:
if(selection = 'Quarter 1', [Sales Target Q1],
if(selection = 'Quarter 2', [Sales Target Q2],
if(selection = 'Quarter 3', [Sales Target Q3],
)))
The problem now is, I want to have a custom total row calculation for the 'Rate(History/User)' using the formula:
Total = Total of Sales Covered / Total of Sales Target
The only way that I can think of is to add this rule to the column 'Rate(History/User)' expression. But, there is already pre-existing expression listed above and it is not related. So, the final table that I'm expecting to see is:
| Employee ID | History Rate | User Provided Rate | Rate (History/User) | Sales Target | Sales Covered |
| Emp 1 | 50% | 70% | 70% | 200000 | 150000 |
| Emp 2 | 65% | 80% | 80% | 350000 | 280000 |
| Emp 3 | 60% | 0% | 60% | 180000 | 125000 |
| Total | | | 76% | 730000 | 555000 |
where 76% = 555000 / 730000 x 100
Any advice or tips?
1 suggestion that friend is suggesting is to add a dummy row called 'Total' and then for each of the column, I need to add additional logic to filter it and only to display the right value (either SUM of the column or data from the field) and set the Total Mode to 'No Totals' which I think not a good idea.
