topic Re: Sub-total for Top 10 values in QlikView

Sub-total for Top 10 values

mattphillip — Wed, 25 Nov 2020 16:16:04 GMT

Hoping someone can help. I have a bar chart with the displayed values limited to the Top 10. What I'd like to calculate is the sub-total for that Top 10 (e.g. if the top values are 20, 15,10,6 then sub-total = 51). I also need to subtotal the entire data (but I think I can handle that one).

Anyone have any ideas?

Much appreciated!

Re: Sub-total for Top 10 values

Gysbert_Wassenaar — Thu, 11 Aug 2016 16:48:22 GMT

Where do you need to display these subtotals?

Re: Sub-total for Top 10 values

sunny_talwar — Thu, 11 Aug 2016 16:48:27 GMT

Do you have a sample you can share where you are trying this out?

Re: Sub-total for Top 10 values

mattphillip — Thu, 11 Aug 2016 17:06:37 GMT

Hi Gysbert,

To be honest I'm not too fussy. Either on the chart itself or in a textbox whichever is easier.

Re: Sub-total for Top 10 values

Gysbert_Wassenaar — Thu, 11 Aug 2016 17:09:42 GMT

The overall total can be added by enabling the Show Total option on the Dimension Limits tab. The top 10 total would be something like

='Total top 10: '& num(sum({<Fruit={"=rank(sum(Quantity))<=10"}>}Quantity),'#,##0')

Re: Sub-total for Top 10 values

mattphillip — Thu, 11 Aug 2016 17:15:27 GMT

Hi Sunny,

Please see attached as requested. Sorry I had to put together a mock version of my dashboard due to data protection.

Re: Sub-total for Top 10 values

sunny_talwar — Thu, 11 Aug 2016 17:20:49 GMT

This?

Script:

LOAD Fruit,

Quantity

FROM

[Top 10 total test.xlsx]

(ooxml, embedded labels, table is Sheet1);

Dim:

LOAD * Inline [

Dim

];

Dimension:

=Pick(Dim, Fruit, 'Top 10 Total', 'Overall Total')

Expression:

=Pick(Dim,

Sum(Quantity),

Sum({<Fruit = {"=Rank(Sum(Quantity)) < 11"}>}Quantity),

Sum(Quantity))

Sorting Expression: Descending

Sum(Quantity)-(Dim*100000)

Re: Sub-total for Top 10 values

sunny_talwar — Thu, 11 Aug 2016 17:26:03 GMT

Forgot to bump the dimension limits by 2

Re: Sub-total for Top 10 values

mattphillip — Thu, 11 Aug 2016 17:34:59 GMT

Thanks Sunny. Now just to make it tricky - on my actual dashboard, instead of 'Fruit', I have a calculated dimension which allows the user to change the field in the chart by a click on a button (=if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)) ). Am I able to simply replace 'Fruit' with this or does this only work with typical dimensions?

Re: Sub-total for Top 10 values

sunny_talwar — Thu, 11 Aug 2016 17:38:07 GMT

I would think it should work. Give it a try and see if that works or not

Re: Sub-total for Top 10 values

mattphillip — Thu, 11 Aug 2016 17:46:38 GMT

It didn't work.

I put the inline DIM table in and then added as below.

Dimension

=Pick(Dim,if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)),'Top 10 Total','Abertay Total')

Expression

=Pick(Dim,

$(vFTEorHC)([FTE]),

$(vFTEorHC)({if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2)) = {"=Rank($(vFTEorHC)([FTE]))<13"}>}[FTE]),

$(vFTEorHC)([FTE]))

Sorting Expression

=$(vFTEorHC)([FTE])-(Dim*100000)

The vFTEorHC variable changes on a button click with two possible values of 'sum' or 'count'. The FTE field replaces Quantity.

Any ideas would be much appreciated. It just comes up with no data to display .

I'll keep checking back tonight but I'm off home now as its almost 7pm here.

Thanks Sunny!

Re: Sub-total for Top 10 values

mattphillip — Thu, 11 Aug 2016 17:55:19 GMT

Hi Gysbert,

Thanks for the suggestion. I tried it but no luck. I've included my version of the code below. 'Fruit' has been replaced with a calculated dimension which allows the user to change the field displayed in the chart. The vFTEorHC variable changes on a button click with two possible values of 'sum' or 'count'. The FTE field replaces Quantity. I tried the below in a text box and got an error message.

='Total top 10: '& num(sum({<if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2))={"=rank($(VFTEorHC)(FTE)<=10"}>}FTE),'#,##0')

Any ideas where I'm going wrong?

Thanks in advance!

Re: Sub-total for Top 10 values

sunny_talwar — Thu, 11 Aug 2016 17:59:33 GMT

I think the issue is in the dimension, can you try this out:

=Pick(Dim,

$(vFTEorHC)([FTE]),

$(vFTEorHC)({<$(=if($(vdimensions2)<>'None' and $(vdimensions2)<>'White',$(vdimensions2))) = {"=Rank($(vFTEorHC)([FTE]))<13"}>}[FTE]),

$(vFTEorHC)([FTE]))

Expression editor might continue to show error in expression, but try clicking ok to see if you get any result or not

Re: Sub-total for Top 10 values

mattphillip — Fri, 12 Aug 2016 08:41:32 GMT

Right I've tried that and now the chart simply comes up with 'Allocated memory exceeded' which is odd as my machine is spec'd higher than most to handle larger data and the data we're using on the current dashboard isn't huge.

Don't suppose you have any other ideas?

Appreciate your efforts

Re: Sub-total for Top 10 values

sunny_talwar — Fri, 12 Aug 2016 09:35:55 GMT

Unfortunately I do not. Until I can see the issue, it is very difficult to find the issue