Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Custom total row calculation on straight table

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 IDHistory RateSales Target Q1Sales Target Q2Sales Target Q3Sales Covered Q1Sales Covered Q2Sales Covered Q3
Emp 150%20000019000015000015000011000055000
Emp 265%35000030000025000028000020000078000
Emp 360%1800001000001500001250008000090000

I have created a QlikView straight table as below:

Employee IDHistory RateUser Provided RateRate (History/User)Sales TargetSales Covered
Emp 150%70%70%200000150000
Emp 265%80%80%350000280000
Emp 360%0%60%180000125000
Total730000555000

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 IDHistory RateUser Provided RateRate (History/User)Sales TargetSales Covered
Emp 150%70%70%200000150000
Emp 265%80%80%350000280000
Emp 360%0%60%180000125000
Total76%730000555000

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.


3 Replies
MVP & Luminary
MVP & Luminary

Maybe this:

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


talk is cheap, supply exceeds demand
Not applicable

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...

Not applicable

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