topic A subtotal in a table in QlikView

A subtotal in a table

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

Hello,

I would like to create in a table a subtotal on a population category. And a total on the whole population. With a peculiarity on the percentages as the following table

thanks you for your help

Re: A subtotal in a table

prat1507 — Thu, 22 Jun 2017 06:56:26 GMT

Can u plz share a sample app.

Regards
Pratyush

Re: A subtotal in a table

Anil_Babu_Samineni — Thu, 22 Jun 2017 07:01:16 GMT

May be this?

Sum(Number)/Sum(TOTAL Number)

Sum(Number)/Sum(TOTAL <Dimension1> Number) //Dimension1 is nothing but your Dimensions

Re: A subtotal in a table

Anonymous — Thu, 22 Jun 2017 07:28:18 GMT

hello,

A sample app

Thanks

Re: A subtotal in a table

prat1507 — Thu, 22 Jun 2017 07:37:18 GMT

PFA the app with the calculations.

Regards
Pratyush

Re: A subtotal in a table

Anonymous — Thu, 22 Jun 2017 07:58:28 GMT

Thanks but i need a subtotal for these catégories

Re: A subtotal in a table

Anil_Babu_Samineni — Thu, 22 Jun 2017 08:33:23 GMT

One simple way, May be this?

Count(Customer) / Count(TOTAL {<slice -= {Unknow}>} Customer)

Re: A subtotal in a table

PrashantSangle — Thu, 22 Jun 2017 08:34:44 GMT

Hi,

Enhancing Pratysh reply.

Convert straight table into pivot table

add calculated dimension as

=if(WildMatch(slice,'0-30 years','30-40 years','40-50 years','50-60 years','60-70 years','70 ans and more'),'Group1',slice)

then go to presentation > select Always Fully Expanded.

Regards,

Prashant Sangle

Re: A subtotal in a table

devarasu07 — Thu, 22 Jun 2017 08:52:14 GMT

Hi,

By using If Valuelist method you can able achieve desired output. please check the sample app attached (you have to change the values based on yours).

ValueList() – For those tricky situations

Missing Manual - ValueLoop() & ValueList()

Re: A subtotal in a table

Kushal_Chawda — Thu, 22 Jun 2017 12:30:06 GMT

Age:

LOAD Age,

Customer,

if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),'Know',

if(slice='Unknow','Unknow')) as Group,

if(Match(slice,'70 ans and more','60-70 years','50-60 years','40-50 years','30-40 years','0-30 years'),slice) as Know_Slice,

if(slice='Unknow',slice) as Unknow_Slice,

slice

FROM

[Age.xlsx]

(ooxml, embedded labels, table is Sheet1);

Sort:

LOAD * Inline [

Know_Slice,Sort

70 ans and more,6

60-70 years,5

50-60 years,4

40-50 years,3

30-40 years,2

0-30 years,1 ];

Dim:

LOAD * Inline [

Dim

4 ];

Create Straight table

Dimension:

=Pick(Dim,Know_Slice,'Under Total',Unknow_Slice,'Total')

=Pick(Dim,

Count(Customer)/Count( total Customer),

Count({<Group={'Know'}>}Customer)/Count(total Customer),

Count(Customer)/Count(total Customer),

Count(Customer)/Count(total Customer))

Sort Expression:

=Pick(Dim,Sort,8,9,10)

Re: A subtotal in a table

Anonymous — Fri, 23 Jun 2017 12:33:09 GMT

thanks you very much

It's perfect

Re: A subtotal in a table

Anonymous — Fri, 23 Jun 2017 12:34:32 GMT

thanks i didn't know this function

Re: A subtotal in a table

noviceneil — Tue, 24 Oct 2017 11:06:16 GMT

Thanks Kushal..Nice example