Re: above() use in a recursive way - Qlik Community

michael_klix · ‎2022-09-06

I need help with an above() formula and most probably, the problem is the recursive type of calculation, why I fail.

I want to visualize the charging/capacity status of a battery, connected to my photovoltaic panel.

Assume, at a given point in TIME, I have some power CONSUMPTION and some power PRODUCTION.

Whenever PRODUCTION>CONSUMPTION, the battery is charged.

As soon as the battery CAPACITY is reached, there is no more charging and energy is lost.

Whenever PRODUCTION<CONSUMPTION, the battery is decharged, until it reaches 0.

As soon as 0 is reached, I need power from the grid.

The following formula is a good start, but it fails to give a correct development of the battery.
This is, why so far I did not even reflect the frame conditions no negative value and no value above CAPACITY. I would just be happy to see the values change 🙂

measure Battery status defined as

(if(RowNo()=1,
CAPACITY + (PRODUCTION-CONSUMPTION),
above(total CAPACITY+ PRODUCTION-CONSUMPTION) + PRODUCTION-CONSUMPTION
)

Any idea how to create a measure that gives the battery status over time?

The resulting table may look like this , if we start at midnight with a full battery, over night battery is decharged and with rising sun until noon it starts charging

The EXCEL formula for the measure below is from the 2nd line
=IF( E2+C3-D3>B3; B3;
IF( E2+C3-D3<0;0;
E2+C3-D3))

TIME	CAPACITY	PRODUCTION	CONSUMPTION	measure
00:00	12	0	0	12
01:00	12	0	0	12
02:00	12	0	3	9
03:00	12	0	4	5
04:00	12	0	5	0
05:00	12	1	6	0
06:00	12	2	2	0
07:00	12	3	3	0
08:00	12	4	2	2
09:00	12	5	3	4
10:00	12	5	2	7
11:00	12	5	3	9
12:00	12	5	3	11
13:00	12	5	3	12
14:00	12	4	3	12
15:00	12	3	2	12
16:00	12	2	2	12
17:00	12	1	2	11
18:00	12	1	2	10
19:00	12	1	1	10
20:00	12	1	1	10
21:00	12	0	1	9
22:00	12	0	0	9
23:00	12	0	0	9

rubenmarin · ‎2022-09-06

Hi, mabye adding a rangesum and the additional parameteres of above to include all above rows, something like:

Rangesum(above(total CAPACITY+PRODUCTION-CONSUMPTION,0, RowNo(TOTAL)))

Maybe capacity will go outside the rangesum to only count it once, but the general idea is to include the above in a rangesum to sum all rown since the current (the '0' parameter') to all above (Rowno(TOTAL) tries to do that)

michael_klix · ‎2022-09-06

rangesum works indeed, but it does only calculate the total aggregation. But I must be able to use the value above to start from and decide if the max CAPACITY is already reached. In that case, the aggregation cannot continue. The battery is simply full.

rubenmarin · ‎2022-09-06

Maybe doing this in script, you can do a sorted load and have a field With CurrentCapacity already loaded.

In script will be easier to limit the capacity, with something like

If(Peek(CurrentCapacity)+Production-Consumption>=MaxCapacity
  ,MaxCapacity
  ,Peek(CurrentCapacity)+Production-Consumption
) as CurrentCapacity

Or · ‎2022-09-06

That looks great to me. Allowing for no negative current capacity and changing the phrasing a bit:

RangeMax(RangeMin(RangeSum(Peek(CurrentCapacity),Production,-Consumption),MaxCapacity),0)

* Rangesum rather than plus minus to avoid nulls breaking the math

* RangeMin and RangeMax rather than restating the formula in if statements

michael_klix · ‎2022-09-06

UUps

I forgot to say that it must be calculated dynamically in a table in the visualisation as there are some variable values to be included in the formula, that can be defined by the user

Or · ‎2022-09-06

Rangesum(CAPACITY,above(total PRODUCTION-CONSUMPTION,0, RowNo(TOTAL)))

Since Capacity repeats for each row, we can add it externally rather than within the total, which prevents messing things up.

That said, getting this to behave correctly with a cap of CAPACITY and a floor of 0 might get tricky, but this should work for a straight running total and you can hopefully work your conditions from there.

michael_klix · ‎2022-09-06

As for CAPACITY, it is right. Actually in my final model it is not a dimension but a variable, set by the user via an input box/slider.

Unfortunately rangesum does not help. The rangesum itself is an easy total aggregation. But the tricky part is that the aggregation must stop once CAPACITY is reached or we are below 0. And here, we get the recursive definition that is not allowed in above()

Maybe someone has an idea, how to solve the recursive limitation problem?

michael_klix · ‎2022-09-07

Meanwhile I found a way to create the table with recursive measures using above() and Column().

This is the resulting Table, the first 5 columns are just repeating the excel, specifically to get column (measure) with the expected result. I used only TIME as dimension and all others as measures to make ranges and above easier. This is why the counting starts with 1 on column (capacity).

This is the resulting table (xls enclosed below):

TIME	(CAPACITY)	(PRODUCTION)	(CONSUMPTION)	(measure)	PRODUCTION-CONSUMPTION	end capacity	start capacity	grid power need
00:00	12	0	0	12	0	12	12	0
01:00	12	0	0	12	0	12	12	0
02:00	12	0	3	9	-3	9	12	0
03:00	12	0	4	5	-4	5	9	0
04:00	12	0	5	0	-5	0	5	0
05:00	12	1	6	0	-5	0	0	5
06:00	12	2	2	0	0	0	0	0
07:00	12	3	3	0	0	0	0	0
08:00	12	4	2	2	2	2	0	0
09:00	12	5	3	4	2	4	2	0
10:00	12	5	2	7	3	7	4	0
11:00	12	5	3	9	2	9	7	0
12:00	12	5	3	11	2	11	9	0
13:00	12	5	3	12	2	12	11	0
14:00	12	4	3	12	1	12	12	0
15:00	12	3	2	12	1	12	12	0
16:00	12	2	2	12	0	12	12	0
17:00	12	1	2	11	-1	11	12	0
18:00	12	1	2	10	-1	10	11	0
19:00	12	1	1	10	0	10	10	0
20:00	12	1	1	10	0	10	10	0
21:00	12	0	1	9	-1	9	10	0
22:00	12	0	0	9	0	9	9	0
23:00	12	0	0	9	0	9	9	0

And this are the measures (BatteryCapacity is a variable, currently set to 12 to match the excel table)

column (6) end capacity

if (rowno()=1,if(BatteryCapacity+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
BatteryCapacity+PRODUCTION-CONSUMPTION
),
if(column(7)+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
if(column(7)+PRODUCTION-CONSUMPTION<0,0,column(7)+PRODUCTION-CONSUMPTION)
)
)

column(7) start capacity

if(RowNo()=1,BatteryCapacity,
above(
if (rowno()=1,if(BatteryCapacity+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
BatteryCapacity+PRODUCTION-CONSUMPTION
),
if(column(7)+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
if(column(7)+PRODUCTION-CONSUMPTION<0,0,column(7)+PRODUCTION-CONSUMPTION)
)
)
)
)

column(8) grid power need (means if Battery is empty and PRODUCTION<CONSUMPTION, we need power from the grid)

if(
(
above(
if (rowno()=1,if(BatteryCapacity+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
BatteryCapacity+PRODUCTION-CONSUMPTION
),
if(column(7)+PRODUCTION-CONSUMPTION>BatteryCapacity,BatteryCapacity,
if(column(7)+PRODUCTION-CONSUMPTION<0,0,column(7)+PRODUCTION-CONSUMPTION)
)
)
)
)

=0 and PRODUCTION-CONSUMPTION<0 ,-(PRODUCTION-CONSUMPTION),0)

I tried to eliminate the "column()" referrence as much as possible but in column(7) there remains that recursive definition with above.

So far so good, BUT...

How can we now put the sum over column(8) into a KPI visual? We get it in the table easily as column sum. But in an KPI field I would assume we need some

sum(aggr("column(8)",TIME).

Is this possible somehow?

above() use in a recursive way (in a diagram, or in KPI with aggr(), NOT script)

Advanced Authoring