Top M of Top N Summary

[Anonymous]

Hi,

I'm currently moving into the Qlikview developer role at work and have started to take some of our old reports which were created in Excel using data from sp in SQL and recreate them in Qlikview. One that I am having difficulty with is an executive summary which shows the top 20 of one field broken down by top 10 and 'Others' of a second field; these are dynamic according to selections.

I've mocked up the report as per the attached (Excel file & Image). I've also made some progress using Aggr and FirstSortedValue(specifically FirstSortedValue([Field Name],-Aggr(SUM({[Summary]} [Measure]),[Field Name]),N) to bring through the Nth ranked Field Name) in a Text box but then don't seem to be able to use this method to produce the Sub Item lists.

Thanks for any help in advance,

Robert

[Oleg_Troyansky]

The quick answer to this question is this - in order to use the AGGR function within charts, you should always add your Chart Dimension(s) to the list of the AGGR dimensions.

AGGR() is one of the most poorly documented functions in QlikView. I teach a half-day lecture at the Masters Summit for QlikView, just on AGGR() and Set Analysis. I'm also describing AGGR() with lots of detailed exercises in my new book, QlikView Your Business.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

Thanks for the reply Oleg.

I’ve managed to get it working in two stages by using variables and then using the variables in a calculated dimension but can’t make the final step

E,g, for the 10th ranked item I have the variable vTop20j defined as:-

=FirstSortedValue(,-Aggr(SUM({[Summary]< = {''} - {'Wholesale','Head Office'},[Transactions.Invoice Status] = {''} - {'Invoice Accepted'}, AsOfPeriod = {$(vInsurerOverviewRoot)}, PeriodType = {'Rolling 12'}>} ),[Insurer Overview Name (Wk)]),10)

And the calculated dimension is:-

=If(='$(=vTop20j)',[Insurer Overview Name (Wk)],Null())

Nulls are then suppressed, the column is hidden and I add in my sub-item field using dimension limits to get top 10 and all works fine.

However I’d like to skip the variables but a straight swap of the variable for its definition in the calculated dimension doesn’t work

Thanks, Robert

[Oleg_Troyansky]

Hi Robert,

something must have happened with the copying and pasting of your expressions, because they look syntactically incorrect...

A few general comments:

- if your variable definition begins with an equal sign, then it's being calculated once after every click and is NOT being recalculated for each line of the chart. The same calculation listed directly within the chart dimension or expression, will get recalculated in the context of each chart line.

- Your calculation begins with the FirstSortedValue() function, which returns a single value (not an array of values). I have doubts that it should work correctly in a calculated dimension. I'd expect the dimension to be defined with the AGGR() function that produces an array of values. However it might be OK in your case, if you are looking for a single value.

If you can post your document, I can troubleshoot the problem directly. Otherwise, let me refer you to my blog, where I describe in detail how these calculations can be made:

Show Top Performers

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[#5 · Accepted solution]

Hi Robert,

Have a look if that helps.

Please provide us a feedback.

Regards

[Anonymous]

Hi Jorges,

I think the dimension formula might be what I'm after although I'll have to add and adapt to my own app first.

Thanks, Robert

[Anonymous]

Thanks Jorge, that worked fine, I can now ditch the 20 variables I was using