Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

mambi
Contributor III

Calculate subtotal, total

Hi,

according to the attached file : i want to calculate the subtotal for each category but the problem is that if the sub categories have the same value then the category also must have this value and if sub-categories have different values then the category must be the sum of them.

so in this exemple :

category 1 subtotal must be 5 and category 2 subtotal = 7 (3+4) and the total 12

thanks for your help

1 Solution

Accepted Solutions
matthias_v
New Contributor III

Re: Calculate subtotal, total

try the following as your expression:

if

(

//0 = grand total

dimensionality() > 0

,

sum(distinct amount)

,

sum(aggr(sum(DISTINCT amount), Category))

)


On a detail-level you only have one amount, so that will stay unchanged.

On category-level a sum will be made on the distinct values within that category

On the grand total the sum(aggr(... will make the summation of the category-level subtotals

4 Replies
Employee
Employee

Re: Calculate subtotal, total

You can get the subtotals the way you want it by using

     if(Count(distinct amount)=1,Only(amount),sum(amount))

but the total will still be 22. I do not think it is possible to get the total the way you want it.

HIC

mambi
Contributor III

Re: Calculate subtotal, total

can i use Dimensionality() ?

matthias_v
New Contributor III

Re: Calculate subtotal, total

try the following as your expression:

if

(

//0 = grand total

dimensionality() > 0

,

sum(distinct amount)

,

sum(aggr(sum(DISTINCT amount), Category))

)


On a detail-level you only have one amount, so that will stay unchanged.

On category-level a sum will be made on the distinct values within that category

On the grand total the sum(aggr(... will make the summation of the category-level subtotals

mambi
Contributor III

Re: Calculate subtotal, total

thanks for you help Sir

Community Browser