Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Highlighted
AB-mx
New Contributor

Subtotal based on different rows in a balance schema

I need to calculate the subtotals based on a dimension = at certain row line...

here the screenshot of what i mean....

RowNrDescriptionRifValue
10Sales0 
20Direct sales70110
30Wholesale70120
40E-Commerce70150
50Europe70150
60America140200
70Total Sales130Here need the total of the rows only with column rif= 70
80Expenses0 
90Material12085
100Production120300
110Administrative120250
120Total Expenses130Here need the total of the rows only with column rif= 120
130TOTAL Net Here need the total of the rows only with column rif=130

 

please help asap

Tags (1)
1 Solution

Accepted Solutions

Re: Subtotal based on different rows in a balance schema

You can try to use an expression like this

=Pick(Match(RowNr, 70, 120, 130) + 1, Sum(Value), Sum(TOTAL {<Rif = {70}>}Value), Sum(TOTAL {<Rif = {120}>}Value), Sum(TOTAL {<Rif = {70, 120}>}Value))

image.png 

Note: In an effort to save time, I have used the sample posted by @StarinieriG (Thank you).

Best,
Sunny

View solution in original post

5 Replies
Partner
Partner

Re: Subtotal based on different rows in a balance schema

Hi,

you could create one expression for each row (see file attached)

AB-mx
New Contributor

Re: Subtotal based on different rows in a balance schema

Thank you,

this could be an idea, but the problem is that the Schema is dynamic, and i can not omit the first column, the client in the system can add rows in the schema when he needs , if i follow your example i will need to add a new expression for the new row everytime he does change ...

Consider even that the total rows in the final schema (balance UE) are hundreds ...

i dont know if i've been clear enough

Any suggestion?

AB-mx
New Contributor

Re: Subtotal based on different rows in a balance schema

Any suggestions? please....

Thank you in advance

Re: Subtotal based on different rows in a balance schema

You can try to use an expression like this

=Pick(Match(RowNr, 70, 120, 130) + 1, Sum(Value), Sum(TOTAL {<Rif = {70}>}Value), Sum(TOTAL {<Rif = {120}>}Value), Sum(TOTAL {<Rif = {70, 120}>}Value))

image.png 

Note: In an effort to save time, I have used the sample posted by @StarinieriG (Thank you).

Best,
Sunny

View solution in original post

AB-mx
New Contributor

Re: Subtotal based on different rows in a balance schema

Thank you so much, it seems perfect ... exactly what i need.