Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
An issue has been identified on Qlik Cloud hub, please visit our Status Update Page for details: GET THE LATEST
cancel
Showing results for 
Search instead for 
Did you mean: 
AB-mx
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

1 Solution

Accepted Solutions
sunny_talwar

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
StarinieriG
Partner - Specialist
Partner - Specialist

Hi,

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

AB-mx
Contributor
Contributor
Author

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
Contributor
Contributor
Author

Any suggestions? please....

Thank you in advance

sunny_talwar

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

AB-mx
Contributor
Contributor
Author

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