Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
ganeshreddy
Creator III
Creator III

Totals and % totals in Qlikview straight table

Hi all,

Is there any way around to show totals and % of totals in straight table as shown in below image

test.PNG

Can any one help.

Thanks,

Ganesh

1 Solution

Accepted Solutions
Frank_Hartmann
Master II
Master II

Ok, here the adapted solution:

LOAD * INLINE [

    Dim

    1

    2

];

LOAD * INLINE [

    Customer, Progress, Open, No Response, Waiting, Canceled

    Ben, 1, 1, 6, 3, 1

    Kevin, 0, 0, 3, 2, 1

    Rob, 0, 1, 2, 1, 0

    Bob, 0, 0, 1, 2, 1

    Steve, 1, 2, 2, 1, 0

Ok];

Dimension:

=Pick(Dim,Customer,'Total')

Expression 1 for canceled:

if(Dimensionality()=0,

num(sum(Canceled)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Canceled))


Expression 2 for progress:

if(Dimensionality()=0,

num(sum(Progress)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Progress))


Expression 3 for open:

if(Dimensionality()=0,

num(sum(Open)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Open))


Expression 4 for no response:

if(Dimensionality()=0,

num(sum(Waiting)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Waiting))


Expression 5 for waiting:

if(Dimensionality()=0,

num(sum([No Response])/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum([No Response]))


Expression 6 for TOTAL:

if(Dimensionality()=0,

num(Column(1)+Column(2)+Column(3)+Column(4)+Column(5),'###%'),

Column(1)+Column(2)+Column(3)+Column(4)+Column(5))

And this is my Output:

Unbenannt.png

hope this helps!

View solution in original post

12 Replies
Frank_Hartmann
Master II
Master II

see attached file,

hope this helps

ganeshreddy
Creator III
Creator III
Author

Hi Frank,

I can not open the qvw files, could you please share any useful community links.

Thanks,

Frank_Hartmann
Master II
Master II

Skript:

LOAD * INLINE [

    Dim

    1

    2

];

LOAD * INLINE [

    Customer, Progress, Open, No Response, Waiting, Canceled

    Ben, 1, 1, 6, 3, 1

    Kevin, 0, 0, 3, 2, 1

    Rob, 0, 1, 2, 1, 0

    Bob, 0, 0, 1, 2, 1

    Steve, 1, 2, 2, 1, 0

];

As dimension in your straight table use:

=Pick(Dim,Customer,'% of Total')

Expressions:

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(sum(Canceled)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Canceled))

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(sum(Progress)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Progress))

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(sum(Open)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Open))

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(sum(Waiting)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Waiting))

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(sum([No Response])/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum([No Response]))

if(Pick(Dim,Customer,'% of Total')='% of Total',

num(Column(1)+Column(2)+Column(3)+Column(4)+Column(5),'###%'),

Column(1)+Column(2)+Column(3)+Column(4)+Column(5))

hope this helps

Frank_Hartmann
Master II
Master II

and for sorting use this expression:

only({1}Dim)  --> Ascending

Frank_Hartmann
Master II
Master II

Do you have any further questions?

If not, then please close this thread by marking correct and helpful answers

Re: How to mark Helpful or correct reply?

ganeshreddy
Creator III
Creator III
Author

Thanks for the valuable reply, but total count row is missing form your example how to get that?

and also % symbol is missing form my % of total rows. FYI I am using 'dimensionality()=0' instead Pick(Dim,Customer,'% of Total')='% of Total',  

Thanks,

Ganesh

ganeshreddy
Creator III
Creator III
Author

I have above mentioned question on

'total count row is missing form your example how to get that?'

sure if i am clear, will close the thread.

Thanks,

Ganesh

Frank_Hartmann
Master II
Master II

Ok, here the adapted solution:

LOAD * INLINE [

    Dim

    1

    2

];

LOAD * INLINE [

    Customer, Progress, Open, No Response, Waiting, Canceled

    Ben, 1, 1, 6, 3, 1

    Kevin, 0, 0, 3, 2, 1

    Rob, 0, 1, 2, 1, 0

    Bob, 0, 0, 1, 2, 1

    Steve, 1, 2, 2, 1, 0

Ok];

Dimension:

=Pick(Dim,Customer,'Total')

Expression 1 for canceled:

if(Dimensionality()=0,

num(sum(Canceled)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Canceled))


Expression 2 for progress:

if(Dimensionality()=0,

num(sum(Progress)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Progress))


Expression 3 for open:

if(Dimensionality()=0,

num(sum(Open)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Open))


Expression 4 for no response:

if(Dimensionality()=0,

num(sum(Waiting)/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum(Waiting))


Expression 5 for waiting:

if(Dimensionality()=0,

num(sum([No Response])/(sum(Canceled)+sum(Progress)+sum(Open)+sum([No Response])+sum(Waiting)),'##%'),

sum([No Response]))


Expression 6 for TOTAL:

if(Dimensionality()=0,

num(Column(1)+Column(2)+Column(3)+Column(4)+Column(5),'###%'),

Column(1)+Column(2)+Column(3)+Column(4)+Column(5))

And this is my Output:

Unbenannt.png

hope this helps!

View solution in original post

ganeshreddy
Creator III
Creator III
Author

If i use new expressions, i am getting out put like this.

Capture23.PNG

But i  need output like this, I need both the rows Total and '% of Total'

Capture24.PNG

Thanks,

Ganesh