topic Re: Subtotal based on different rows in a balance schema in QlikView

Subtotal based on different rows in a balance schema

AB-mx — Sat, 16 Nov 2024 19:58:21 GMT

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

here the screenshot of what i mean....

RowNr	Description	Rif	Value
10	Sales	0
20	Direct sales	70	110
30	Wholesale	70	120
40	E-Commerce	70	150
50	Europe	70	150
60	America	140	200
70	Total Sales	130	Here need the total of the rows only with column rif= 70
80	Expenses	0
90	Material	120	85
100	Production	120	300
110	Administrative	120	250
120	Total Expenses	130	Here need the total of the rows only with column rif= 120
130	TOTAL Net		Here need the total of the rows only with column rif=130

please help asap

Re: Subtotal based on different rows in a balance schema

StarinieriG — Wed, 02 Oct 2019 13:12:16 GMT

Hi,

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

Re: Subtotal based on different rows in a balance schema

AB-mx — Wed, 02 Oct 2019 14:01:09 GMT

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?

Re: Subtotal based on different rows in a balance schema

AB-mx — Fri, 11 Oct 2019 13:14:37 GMT

Any suggestions? please....

Thank you in advance

Re: Subtotal based on different rows in a balance schema

sunny_talwar — Fri, 11 Oct 2019 13:33:36 GMT

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))

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

Best,
Sunny

Re: Subtotal based on different rows in a balance schema

AB-mx — Mon, 14 Oct 2019 08:00:24 GMT

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