What set analysis to be use in getting grand total reflects as new column?

[kristeljoymalapitan]

Good Day,

Please help me on what set analysis/functions to be use on  below sample data

 I need to add new column 'Total' on the right side and 'Grand total' at the bottom. (highlighted on red font)

Total = total count of accounts per ROLLRATES and per BUCKET .

Please refer to sample output and raw data for your reference

Hope to hear you the soonest.

Thank you

[pradosh_thakur · Accepted solution]

dimension =   BUCKET

MEASURE 

=COUNT({ROLLRATES={'RB'}}BUCKET)

=COUNT({ROLLRATES={'RF'}}BUCKET)

=COUNT({ROLLRATES={'ST'}}BUCKET)

 total 

=column(1)+column(2)+column(3)

sum of rows will give you the grand total anyways.

[gn_marvs1989]

Have you tried to use the "Partial Sum" using Pivot Table?

[kristeljoymalapitan]

Hi  Pradosh_Thakur,

As per trying below function under measure 'error in expression' was encountered.

MEASURE 

=COUNT({ROLLRATES={'RB'}}BUCKET)

=COUNT({ROLLRATES={'RF'}}BUCKET)

=COUNT({ROLLRATES={'ST'}}BUCKET)

[kristeljoymalapitan]

I haven't tried that since I'am new to Qlik Sense and still exploring, Hope to hear from you for me to get positive results.

[pradosh_thakur · Accepted solution]

ahh.. My bad. missed <>

=COUNT({<ROLLRATES={'RB'}>}BUCKET)

=COUNT({<ROLLRATES={'RF'}>}BUCKET)

=COUNT({<ROLLRATES={'ST'}>}BUCKET)

[kristeljoymalapitan]

It works! 🙂 and I named it as RB, RF and ST and use as per below function

What do you mean by this? Is this still under MEASURE?

total 

=column(1)+column(2)+column(3)

I tried below but 'error in expression' error was encountered

=column('RB')+column('RF')+column('ST')

[pradosh_thakur]

just try with RB+RF+ST
OR
if RB is 1st column from the measure then column(1) will give you RB column value and so on. You can check by just putting =column(1) in the expression
=column(1)+column(2)+column(3)

[kristeljoymalapitan]

Thank you so much it works! 🙂

How about total at the bottom? Is it possible? 

[pradosh_thakur]