topic Re: Top N Dimension Limits Sub-Total for Customers in QlikView

Top N Dimension Limits Sub-Total for Customers

Thu, 02 Apr 2015 18:24:27 GMT

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.

Re: Top N Dimension Limits Sub-Total for Customers

Anonymous — Thu, 02 Apr 2015 18:38:09 GMT

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

Re: Top N Dimension Limits Sub-Total for Customers

Fri, 03 Apr 2015 07:26:59 GMT

hii

try this expression

= sum (Total <Operator> Value)

for the subtotals use <> after Total.

and check this thread

Qlikview Subtotaling Customization |

Re: Top N Dimension Limits Sub-Total for Customers

Fri, 03 Apr 2015 17:52:06 GMT

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.

Re: Top N Dimension Limits Sub-Total for Customers

Fri, 03 Apr 2015 18:30:09 GMT

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.

Re: Top N Dimension Limits Sub-Total for Customers

Sat, 04 Apr 2015 03:39:13 GMT

Hi,

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

Priyantha.

Re: Top N Dimension Limits Sub-Total for Customers

Sun, 05 Apr 2015 04:19:25 GMT

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

Re: Top N Dimension Limits Sub-Total for Customers

preminqlik — Sun, 05 Apr 2015 05:25:17 GMT

hi find attachment

hope this helps you

Premhas

Re: Top N Dimension Limits Sub-Total for Customers

Thu, 09 Apr 2015 04:24:42 GMT

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.

Re: Top N Dimension Limits Sub-Total for Customers

preminqlik — Sun, 03 May 2015 03:30:48 GMT

good