Custom total row calculation on straight table

Fri, 20 Mar 2015 09:22:34 GMT

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.

Re: Custom total row calculation on straight table

Gysbert_Wassenaar — Fri, 20 Mar 2015 10:05:48 GMT

Maybe this:

If(dimensionality()=0, sum([Sales Covered])/sum([Sales Target]), rangemax([History Rate],InputFieldUserRate))

Re: Custom total row calculation on straight table

Fri, 20 Mar 2015 10:57:54 GMT

Ok, thanks but, problem is both 'Sales Covered' and 'Sales Target' columns is just column name and not field name...so it will end up 'bad field name' error.

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],

)))

For 'Sales Covered',

i added the expression that will detect the selection of the menu:

if(selection = 'Quarter 1', [Sales Covered Q1],

if(selection = 'Quarter 2', [Sales Covered Q2],

if(selection = 'Quarter 3', [Sales Covered Q3],

)))

Any other easier/better way to resolve this? Sounds like I have add additional nested if statement before the SUM...

Re: Custom total row calculation on straight table

Sun, 22 Mar 2015 07:45:42 GMT

did you try using a pick match combination instead of the nested if statement?

topic Re: Custom total row calculation on straight table in QlikView

Custom total row calculation on straight table

Re: Custom total row calculation on straight table

Re: Custom total row calculation on straight table

Re: Custom total row calculation on straight table