subtotal of a subtotal

[#1]

Hi all,

I've got following table (pivot). I've got the sum of "Aantal" / Total Sum of "Aantal".

The second expression is the % of that.

What I want is the "Aantal" of a subtotal divided trough the Total of "Aantal"

Example: DAMES 53 has "Aantal" 404. So "Kleur" Blauw has 350 / 404 = 86,63% instead of 350 / 1739 = 20,13%.

Does anybody knows a soluation for this? something with aggr perhaps. I'm a little bit lost.

Thanks,

Rey-man

[#2]

Hi Rey-Man ,

I am in Data analysis & quite new to Qlikview ,but i think u may add one more measure "Aantal" / Total Sum of "(Aantal+Grootte)" (eg ....... "Aantal" / sum (distinct (Aantal+Grootte) ) ).

Wht i want to convey is grouping of combination of "Aantal+Grootte" will resolve your things.

let me know in case of any doubt.

Regards,

Bhushan N

[blaise]

This expression would do the trick. Note that I've used your column names as field names so you need to change thoose to the actual fields.

if(RowNo()=0,(sum(Aantal) / sum(total <[Dames / Heren]> Aantal)),if(isnull(RowNo()),(sum(Aantal) / sum(total <Artikel> Aantal)),(sum(Aantal) / sum(total <Grootte> Aantal))))

[#4]

Dear Ray-man,

Try using following:-

sum(total <Artikel,Dames,.................> Aantal)

here in angle brackets you can give a comma saperated list of dimensions you want to regard.

May be this can help you.

Thanks & Best Regards,

Kuldeep Tak

[#5]

You can use the total qualifier based on dimensionality. Since you have 4 dimensions, it will be something like this i guess:

sum(Aantal)/

if(dimensionality()=4,sum(total <Grootte> Aantal),

if(dimensionality()=3,sum(total <Dimension2> Aantal),

if(dimensionality()=2,sum(total <Artikel> Aantal)

)))

[blaise]

didn't know about the dimensionality() function. More flexible than the RowNo() sometimes and a bit more elegant 🙂

[#7 · Accepted solution]

Hi Ray-man,

Check the attached qvw for the reference.

Thanks & Best Regards,

Kuldeep Tak

[#8]

Tnx, Kuldeep,

this works perfectly

Rey-man