Top N Dimension Limits Sub-Total for Customers

[#1]

Hello There!

In a Straight Table, I've selected Restriction 'Show Only Largest N Values' and also 'Show Others' check-box. Now, I want to see Sub-Total after display of Top N Customers individual Sales Data. Then Other's Total and lastly Grand Total.

Below is a sample format:-

               Customer Name          Sales Amount

               Customer# 01               5000

               Customer# 02               4000

               Customer# 03               3500

               Customer# 04               3000

               Customer# 05               2500

               Sub-Total                  18000

               Others                       42000

               Grand-Total              60000

Till now, I've not achieved to have Sub-Total as above in the format; rest I did managed. Can anyone please suggest any way to achieve this?

Thanks in Advance.

Sajjad.

[Anonymous]

Try something like

Sum({< {"Customer Name=Rank(SUM( Sales Amount))<=5"}>} Sales Amount) for subtotal

use above() function to get Grand Total from Others and Sub-Total

[#3]

hii

try this expression

= sum (Total <Operator> Value)

for the subtotals use <> after Total.

and check this thread

Qlikview Subtotaling Customization |

[#4]

Hello Praveena,

Thanks for your response. Since I'm new in QV, would appreciate if you elaborate your solution for my better understanding.... say, where should I set the expression for SubTotal ?

Also please specify where I should 'use above() function to get Grand Total from Others and Sub-Total'?

Appreciate your prompt response. Thanks in Advance.

Sajjad Ali.

[#5]

Hello Navven,

Thanks for your response. Please pardon me for my limitation since I'm new in QV......, would appreciate if you elaborate a bit where should I set the expression for SubTotal? In Expression Tab of Straight-Table Chart Property?

Also I've gone through your given link "Qlikview Subtotaling Customization" which talks about Pivot-Table Chart but I'm using Straight-Table Chart; hence that Dimensionality may not applicable for my case, I suppose.

Appreciate your views and suggestions in response. Thanks in Advance.

Sajjad Ali.

[#6]

Hi,

Use the praveena's  suggestion in the sales amount field.

Priyantha.

[#7]

Hello!

You could try this workaround which doesn't use dimension limits..create two straight tables

FIRST TABLE:

Dimension :  Customer Name

Expression: Sum({<[CUSTOMER NAME]={"=Rank(SUM(QTY))<=6"}>}QTY) 

Label: SUB TOTAL

SECOND TABLE:

Calculated Dimension: ='Others'

Expression: =pick(match(Dimensionality(),1,0),Sum({<[CUSTOMER NAME]={"=Rank(SUaM(QTY))>6"}>}QTY) ,sum(QTY))

Label: GRAND TOTAL

Match the two table layout and formatting and align one below the other so that they look like one table…Hope this works

[preminqlik]

hi find attachment

hope this helps you

@

Premhas

[#9]

Dear Premhas,

Thanks for your solution and attachment.

It's really neat, cool and smart solution. And works fine.....

Appreciate a lot for QVW attachment .... Thank You again...

Best Regard,

Sajjad.

[preminqlik]

good