13 Replies Latest reply: Jul 22, 2015 9:57 PM by Jim Chan

# how to create a subtotal in a straight table?

HI there,

I want to create a straight table with subtotalling. I mean - a total figure for my top 3 and a grand total figure in this straight table.

Customer_Name

Amount
customer110
customer29
customer38
Total of TOP 327
GrandTotal

50

How can i achieve that using Dimension limits settings in straight table?  I wasnt able to get the top 3 total - 27, and only able to show the grand total and the balance amount of this.

Rgds,

Jime

• ###### Re: how to create a subtotal in a straight table?

Hi, Fuan lik Chan.

See if this help.

In Dimension

Aggr(

if(Rank(Sum(Amount))<=3,Customer_Name,'Top 3')

,Customer_Name)

In Expression

Aggr(

if(Rank(Sum(Amount))<=3,Sum(Amount),Sum(TOTAL Amount))

,Customer_Name)

Access the properties of straight table:

Presentation aba

Totals on Last Row in Totals;

Sort aba

Numeric Value  Ascending.

Hope this helps!

• ###### Re: how to create a subtotal in a straight table?

nope. not working Jonas. or maybe can you attached a qvw file here?

• ###### Re: how to create a subtotal in a straight table?

Hi,

Please find attached file, hope it helps you.

Jagan.

• ###### Re: how to create a subtotal in a straight table?

Hello Mohan,

I did this without any scripting. The QVW file that you sent, i think requires user to load in very long script. Does the qvw file that you sent, able to BOLD - top 3 and Total?

Rgds.

Jim

• ###### Re: how to create a subtotal in a straight table?

Hi,

Replace that Load script with your actual script, it is just for sample purpose.

Regards,

Jagan.

• ###### Re: how to create a subtotal in a straight table?

Hi

PFA

• ###### Re: how to create a subtotal in a straight table?

thanks for your feedback too.

• ###### Re: how to create a subtotal in a straight table?

Hi.

P.S.: Sorry I had to change the expressions. In my data warehouse worked the post aforementioned.

Hope this helps!

• ###### Re: how to create a subtotal in a straight table?

Hi.

In your Expression's tab, go to the section that says "total mode" and change the radio button to the 3rd option and make it "sum of rows". in the presentation's tab change the "totals" label to "top 3"

in your dimension limit's tab - check the box that says show totals - and give it an appropriate label:  "grand total"

that will restrict the "top 3" calculation to only calculate on what is displayed in the table. be sure to deselect show others in your dimension limits tab.

Gareth

• ###### Re: how to create a subtotal in a straight table?

I have done it, same as yours, what if you locate the Total to Total on Last Row. ??

can you still show the subtotal then only grand total?

• ###### Re: how to create a subtotal in a straight table?

Hi Fuan,

correct me if i am wrong but I believe qlikview has its own ranking system and because the same expression can be done over multiple dimensions it will always say that the dimension calculation is a subset of the expression calculation (even with only 1 dimension) and therefore always put the dimension total closer to the data set

i.e: Grand total is a dimension calculation and top3 is an expression calculation (limited to the dimension restrictions places by saying sum of rows) therefore grand total will be on the bottom when totals are above and grand total will be at the top when the totals are below thus ensuring it is closer to the data when displaying.

in your case it is not optimal but it has its purpose when looking at badly labeled data models.

Sorry to disappoint but hopefully you can live with it.

Gareth

p.s. if you use my solution - please make thumbs up or make correct answer

• ###### Re: how to create a subtotal in a straight table?

nope. i didnt use your solution. because i have done that with same method, before you have posted up.

But thanks for the feedback.

• ###### Re: how to create a subtotal in a straight table?

Please see the attached