Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Pivot Table and sub-totals by category

Hi all,

I have some troubles using the pivot table in Qlik Sense.

I'm using the pivot table in fully expended mode. My model here is pretty simple, 2 tables :

Table 1 is containing "banks" and table 2 is containing "assets", each asset is linked with 1 bank.

As you can see in the screen below, my bank rows in the table are displaying a "total" by bank. But this total here, is not the sum of all assets linked with this bank, but the average. I would like to display a simple sum instead of this average. Is it possible ?

Banks.PNG

(don't worry the screen is displaying testing data)

Thanks for your help.

1 Solution

Accepted Solutions
Ivan_Bozov
Luminary
Luminary

Gregory Di Pasquale wrote:

Here is my code :

Sum({$<[asset_class] = {'Equity'}>}[asset_value])/Sum({$<[currency_id] = {$(=currencies)}>}currency_rate)

So you divide A by B. This is how your total is calculated as well.

vizmind.eu

View solution in original post

5 Replies
Ivan_Bozov
Luminary
Luminary

What is your expression here? Is it AVG()? The total rows show the total values of the underlying rows, so if you calculate average, the totals will be average as well.

vizmind.eu
Anonymous
Not applicable
Author

Here is my code :

Sum({$<[asset_class] = {'Equity'}>}[asset_value])/Sum({$<[currency_id] = {$(=currencies)}>}currency_rate)

Ivan_Bozov
Luminary
Luminary

Gregory Di Pasquale wrote:

Here is my code :

Sum({$<[asset_class] = {'Equity'}>}[asset_value])/Sum({$<[currency_id] = {$(=currencies)}>}currency_rate)

So you divide A by B. This is how your total is calculated as well.

vizmind.eu
Anonymous
Not applicable
Author

Thanks you're right !

So now I need to find a way to apply my division.

This division is just a currency conversion, I need to use the currency_rate associated with the currency_id = $(=currencies), any idea ? (without using a Sum)

Ivan_Bozov
Luminary
Luminary

Maybe you can do the calculations in the script and have a filed showing the values in one currency. You can use ApplyMap to map the currency to the exchange rate.

A quick search in the community leads to this thread that can help you: How to use currency exchange rates in Qlik Sense

vizmind.eu