How to calculate and show parts of several subtotals in the table?

[NenadV]

Hi, what is the best way to get parts of a whole, when there are several subtotals?

For example:

Thanks

[Marijn]

Hi @NenadV ,

Probably best to put it in a Pivot Table (is a chart in qlikview)

then activate 'Show Partial Sums' in de Presentation tab of the chart properties. You can activate the subtotals for different dimensions, play around a bit to get the desired results.

Regards,

Marijn

[NenadV]

This is not possible using just the pivot table. Thanks anyway

[NenadV]

I've found the initial solution myself by using SetAnalisys, but it works only for the first subtotal.

e.g. sum({$<Year={$(=Year)}>} Value) / sum(total {$<Line={"T_SubTotal1"}, Year={$(=Year)}>} Value)

Any tip on how to overcome this issue?

Thanks 

[stevedark]

Hi @NenadV 

I presume that in your source table you have a table with columns of Line and Group, like this:

Line,Group
Detail 1,Group 1
Detail 2,Group 1
Detail 3,Group 1
Detail 4,Group 2
Detail 5,Group 2

etc.?

Now, because in the first column of your table you want to have both lines and groups (rather than having two columns) you need to create a new dimension which has both. In this new dimension each Line needs to associate with itself and each Group needs to associate with each line. It requires that there is a 1-to-1 relationship between Line and Group.

You can create this new dimension with a pair of RESIDENT loads:

NewDimension:
LOAD DISTINCT
   'Line' as LineType,
   Line,
   Line as [Line and Total]
RESIDENT MyData;

LOAD DISTINCT
   'Group' as LineType,
   Line,
   Group as [Line and Total]
RESIDENT MyData;

Once you have that you can use the Line and Total dimension in your chart, instead of line, and you will have the totals in there also. It's important that you get the sort order right, so that you sort by Group, then LineType and finally Line to get things in the right order.

Your absolute columns should then give the correct totals without doing anything else. You should be able to change the text to be bold and the background colour on the total lines, by looking at the LineType field.

To get the contribution percentages you need to do the following expression:

sum(Value) / sum(total <Group> Value)

You can then add the set analysis to get the correct periods for P1 and P2 columns.

This blog post describes in more detail the applications of creating extra dimensions, for things like aggregated totals: https://www.quickintelligence.co.uk/qlikview-accumulate-values/

Hope that helps.

Steve

[NenadV]

Hi Steve,

I believe I’ve followed all of your instructions.

Also, as an alternative, we can have two dimensions, one for Group and one for Line.

Thanks

[NenadV · Accepted solution]

I'll re-work the data part and bring in sequences of lines and groups to get a required sorting of lines and groups and use the pivot table. 

[stevedark]

Hi @NenadV 

You were mixing solutions in the app, with both the method I was suggesting and using a pivot table.

When I made it a straight table though the sort still didn't behave as I was expecting. The thing which is an issue is when sorting by expression the result must be a numeric value.

You can use the Ord function to turn a character into a number, but you have to do that a character at a time.

I instead opted for adding sort values in the load script (using ORDER BY and AutoNumber) and then sorting on those.

The script became:

Totals:
load distinct
'Group' as LineType,
Line,
Group as LineAndTotal,
9999999 as LineSort,
AutoNumber(Group) * 10000000 as GroupSort
resident Fact
order by Group
;

Map_GroupSort:
MAPPING LOAD
LineAndTotal,
GroupSort
RESIDENT Totals;

Totals:
load distinct
'Line' as LineType,
Line,
Line as LineAndTotal,
rowno() as LineSort,
ApplyMap('Map_GroupSort', Group, -1) as GroupSort
resident Fact
order by Line
;

This then makes the sort by expression super-simple, thus:

=GroupSort + LineSort

The expression was not then working out the total by group correctly, so I added the Group as a dimension to the table and then hid it in the properties.

The attached shows the working result.

Steve

[NenadV]

Hi @stevedark,

You are absolutely right. I've changed the approach to get closer to delivery.

I'll check your proposal and provide my feedback.

Thanks a lot

[stevedark]

Hi @NenadV 

I was working on something else where I was needing to adjust sort order (this, in fact) and realised that you can use rank(minstring(Field)) to get a value you can sort on by expression.

Using this you can then go back to the simple load script of:

Totals:
load distinct
'Group' as LineType,
Line,
Group as LineAndTotal
resident Fact;

load distinct
'Line' as LineType,
Line,
Line as LineAndTotal
resident Fact;

And then have this as a sort expression:

=(rank(TOTAL minstring(Group)) * 1000000) - if(LineType='Line',(rank(TOTAL minstring(Line))), 0)

Revised app attached.

Steve