Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor
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
Highlighted

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
Highlighted
Partner
Partner

Re: Subtotal based on different rows in a balance schema

Hi,

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

Highlighted
Contributor
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?

Highlighted
Contributor
Contributor

Re: Subtotal based on different rows in a balance schema

Any suggestions? please....

Thank you in advance

Highlighted

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

Highlighted
Contributor
Contributor

Re: Subtotal based on different rows in a balance schema

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