Simulate a dynamic stock with variables

[artav]

Hello everyone,

I want to simulate the behaviour of a battery on a network. In my entry data I have a timestamp associated to a value that is a need from the network (energy speaking). I have set up two measures : Need and Production that compute for each timestamp the total need and the production (that production is dependent on variables too).

I have created a variable vCapBat for the capacity of the battery, and two measures: Overproduction and Deficit which compare the need and the production.

I would like to simulate the behaviour of the battery in that way:

• If Overproduction > 0,
  • If StockN-1 < Cap bat,
    
    • StockN = StockN-1 + SurplusElec until Cap bat
  • Else,
    
    • StockN = StockN-1
• If Deficit > 0,
  
  • If StockN-1 > 0,
    • StockN = StockN - 1 - Deficit until 0
  • Else
    
    • StockN = StockN - 1 ( which is 0)

The closest I have reached in the Qlik syntax is:

RangeMin(
    vCapBat*1000000,
    RangeMax(
        0,
        RangeSum(
            Above(Overproduction - Deficit, 0, RowNo())
        ),
    )
)

But it is not so accurate, sometimes I have some overproduction that is not increasing that measure and I noticed that, if I put the RangeMax() part in a column, when there is overproduction it piles up to infinite, then the deficit makes it decrease but it has to go below the battery capacity to change the value of the whole RangeMin(), otherwise this one stays stuck at the battery capacity when the RangeMax() part > Battery capacity.

[rubenmarin]

Hi, I think this would be easier to calculate on script, loading from a table sorted by timestamp where you can have a field with the accumulated value, using Peek() you can access the previous record, checking the accumulated value add adding, substractimg or if it is at max, or min levels, leaving as it was before.

Adding a table with the values laoded you can adjust the conditions until it returns the expected values.

About the question: adding a file with sample data will help to get a correct answer.

[artav]

Hello, sorry I could not answer quicker! I thought of that, but I need to use variables, especially for the production as I can have multiple sources with a power to set. So the idea is to let that be computed in the analytics and not in the script, as it is way better for the use of the application for the client.

[marcus_sommer]

I could imagine that you need some more conditions - querying the results of Overproduction and Deficit as well as their difference + accumulation. Applied with n if-loops and/or wrapping them with further rangemax/rangemin() and/or alt() to exclude any invalide values.

[artav]

Hello @marcus_sommer, thanks for your insights! It is actually not the first formula that I tried, I also tried to set up different variables/measures to compute on one side the accumulation without any decrease, and the differences (as I said I tried two columns, one when the production exceeds the needs and another one that compute the opposite), but my problem is that I never managed to make it completely accurate, especially when I was trying to use the above() function.

[marcus_sommer]

I think I would use a table-chart and calculating each sub-part as an own expression - maybe ending with a dozen of measures or more to detect the failing ones and finding appropriate adjustments.

In this regard be aware that the second and third parameter of the above() may need also a calculation (with/without conditions) and/or that the object-dimensionalities + data-set may require TOTAL statements within the above() and/or rowno() and/or that some parts need a different calculation - maybe depending on the dimensionality() - respectively another calculation-context (could be done with appropriate aggr() wrappings).

It may something logically but also that the data-set and/or the data-quality doesn't support the wanted view.