Skip to main content

New to Qlik Sense

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

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $400 before Dec 31st: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
jaymeknapp
Contributor II
Contributor II

How do I calculate the ratio of a sum total by each number of rows.

Hello - I'm trying to add a column to the below table that is the % of the total in the 2nd column:

Sub ClassGrowth $% of Total Growth
GPT$1,50016%
CRM$2,50026%
Quantitative$3,80040%
ID$95010%
Custom$8509%

 

The 2nd column (Growth $) is the following set analysis:

Sum({$<Year={'2018'},
[Transaction Line Accounts Account Number]={404100},[Transaction Type]={'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])
-
Sum({$<Year={'2017'},
[Transaction Line Accounts Account Number]={404100},[Transaction Type]={'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])

 

Can someone point in the right direction to adding the correct %'s in the 3rd column (% of Total)??

 

Labels (3)
1 Solution

Accepted Solutions
sunny_talwar

Try this

RangeSum(
Sum({$<Year = {'2018'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount]),
-Sum({$<Year = {'2017'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])
)
/
RangeSum(
Sum(TOTAL {$<Year = {'2018'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount]),
-Sum(TOTAL {$<Year = {'2017'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])
)

 

View solution in original post

3 Replies
dplr-rn
Partner - Master III
Partner - Master III

I am assuming you mean [value in column2]/[total of column2]

you need to use TOTAL qualifier. to put simply

your_expression divided by your_expression with total qualifier added

https://community.qlik.com/t5/Qlik-Design-Blog/What-does-the-TOTAL-qualifier-do/ba-p/1472990

 

sunny_talwar

Try this

RangeSum(
Sum({$<Year = {'2018'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount]),
-Sum({$<Year = {'2017'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])
)
/
RangeSum(
Sum(TOTAL {$<Year = {'2018'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount]),
-Sum(TOTAL {$<Year = {'2017'},
[Transaction Line Accounts Account Number] = {404100},[Transaction Type] = {'Invoice','Cash Sale','Cash Refund','Credit Memo', 'Bill', 'Journal'},
$(vExcludeDateFields)>} [Transaction Line Gross Amount])
)

 

jaymeknapp
Contributor II
Contributor II
Author

Thank you! Works perfectly!