Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Qlik Community
- :
- Forums
- :
- Analytics
- :
- App Development
- :
- Re: above() use in a recursive way

Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

michael_klix

Creator II

2022-09-06
06:27 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

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 |

564 Views

8 Replies

rubenmarin

MVP

2022-09-06
06:37 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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)

559 Views

michael_klix

Creator II

2022-09-06
06:43 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

556 Views

rubenmarin

MVP

2022-09-06
06:48 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

MVP

2022-09-06
07:00 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

Creator II

2022-09-06
08:44 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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

529 Views

Or

MVP

2022-09-06
09:15 AM

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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.

514 Views

michael_klix

Creator II

2022-09-06
11:01 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?

498 Views

michael_klix

Creator II

2022-09-07
09:03 AM

Author

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content

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?**

481 Views