Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
Not applicable

Can I show subtotal in qlik sense??

I want to show sub total of each group of dimensions but it doesn't gave me a result I desired.

At the table result i got, I used sum(total <[Company Name]> [Money Raised]) for the measure

excel.PNG

highlighted in yellow is what i want to do. and I use pivot table!

could anyone help me?

1 Solution

Accepted Solutions
xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

Can you load the [Money Raised] using subfield function to have currency and pure number separately?

For example

load

      left([Money Raised],1) as [Currency Symbol],

      num(mid([Money Raised],2)) as [Money Raised],

      other fields ...

From data source;

Then in the front, you can define the measure by:

only([Currency Symbol]) & sum([Money Raised])

Here are an example:

Temp:
LOAD * INLINE [
Fruit, Country, City, Sales
Apple, CN, Beijing, ¥100
Apple, CN, Shanghai, ¥200
Apple, CN, Chengdu, ¥300
Apple, CN, Dalian, ¥400
Apple, USA, LA, $500
Apple, USA, NY, $600
Apple, USA, TX, $700
Orange, NZ, Auckland, $800
Orange, NZ, Wellington, $900
Orange, USA, LA, $1000
]
;

Table:
NoConcatenate
load
Fruit,
Country,
City,
left(Sales,1) as Currency,
mid(Sales,2) as Sales
Resident Temp;
drop table Temp;

In the front:

Hope this can help.

Cheers.

Fei

10 Replies
xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

sum([Money Raised]) is fine. Turn on "Show totals" under dimension3 property.

Not applicable

Re: Can I show subtotal in qlik sense??

oh.. thank you!!

but I have another problem... actually i have currency symbol for all the numbers...  and i think they can't calculate it because it's not pure number.(actually it's a dual() format, but it's not working.

all of them show '-', could you also know solution for this??

xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

Can you make them into number by using num() in the script?

Not applicable

Re: Can I show subtotal in qlik sense??

but I want numbers to have currency symbol...

xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

If the data is number, you can define the format to have currency symbol. Are you saying the currencies re different?

Not applicable

Re: Can I show subtotal in qlik sense??

yes... currencies are dirrefent by numbers...

xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

Does it make sense to sum up numbers with different currencies? So a total number 5000 includes USD3000 and JPY 2000 for example?

Not applicable

Re: Can I show subtotal in qlik sense??

currency for one group is the same. but different from groups...

xufei123
Not applicable

Re: Can I show subtotal in qlik sense??

Can you load the [Money Raised] using subfield function to have currency and pure number separately?

For example

load

      left([Money Raised],1) as [Currency Symbol],

      num(mid([Money Raised],2)) as [Money Raised],

      other fields ...

From data source;

Then in the front, you can define the measure by:

only([Currency Symbol]) & sum([Money Raised])

Here are an example:

Temp:
LOAD * INLINE [
Fruit, Country, City, Sales
Apple, CN, Beijing, ¥100
Apple, CN, Shanghai, ¥200
Apple, CN, Chengdu, ¥300
Apple, CN, Dalian, ¥400
Apple, USA, LA, $500
Apple, USA, NY, $600
Apple, USA, TX, $700
Orange, NZ, Auckland, $800
Orange, NZ, Wellington, $900
Orange, USA, LA, $1000
]
;

Table:
NoConcatenate
load
Fruit,
Country,
City,
left(Sales,1) as Currency,
mid(Sales,2) as Sales
Resident Temp;
drop table Temp;

In the front:

Hope this can help.

Cheers.

Fei