How to use a value in the resultant row to calculate the value in the next row in the same column? - Maybe an advanced Above() function?

[Apoorva_sd]

Hello,

I am trying to create a table in Qlik Sense which is similar to the one below. But I am unable to do it because I cannot pick previous value from the resultant row and use it to calulate the next value in the same column.

Table:

In the above table, Availability is what I am trying to calculate. Formula for that would be "Column A-Column B" for priority 1 which will be 30 hours here for project 1. Then for priority 2, Result will be 30-3=27 i.e., Total Reult hours minus the hours required for 1st priority project. Then for the 3rd priority it will be 27-1=26 and so on. Question is how to use the value from resultant (Result) column to calculate the value for next row in the same column. Is there a function for this? 

Thank you,

Apoorva.

[marcus_sommer]

You have already with above() the suitable function but you need to develop the advanced stuff in dependency to your data and requirements - maybe with something like this:

if(Condition1, 30,
if(Condition2, 30 - rangesum(above(Total sum(Hours), 0, rowno()))))

The bold parts are depending on your data and the intended complexity of your object - means if there are a lot of different months + projects + priorities with different rules you will need more if-loops and you may also need some kind of dynamically calculation of the second and third parameter from the above() and if there are further dimensions included to wrap all this with aggr() functions.

Such things could become quite complex and therefore I suggest to keep the requirements as simple as possible or to move the task into the script in which you could also use interrecord-functions with peek() and previous() within a sorted resident-load.

[Apoorva_sd]

Hi Marcus,

Thank you for the response!

Right! So, with the above() function and if conditions it only worked for 1st two rows. After that the above() function started picking values from Column A-Column B. I want it to be dynamic instead of specifying everything in the formula.

Okay, maybe I will try to do this in the script side. Do you have a sample script of how to use peek() and previous() functions here?

Thank you,

Apoorva.

[marcus_sommer]

Essentially for doing this with the script is a sorted resident-load, like:

...
resident X order by Project, Date, Priority;

and then the above mentioned if-loops query something like this:

if(Project = previous(Project), peek('Result') - Hours, YourProjectValue) as Result

[Apoorva_sd]

Hi Marcus,

Thank you for sharing the sample script. I tried the above suggestion but not fully successful yet. I did sort the resident and then tried creating the formula for the result column using previous and peek, but the problem here is that there are other resources who have tasks for same projects. My data looks something like below (1st picture). So, even if I sort the table by Priority when I use previous() function it isn't taking the previous value of same person but rather than the previous value of a different person who has been assigned the same priority project. So, need some help on how to sort the table by Resource Name and Priority so that the sorted table looks like second picture below.

i.e., Create the table which is sorted by Priority and shows all data for one resource for the entire month. I tried group by and order by but it is not working. Maybe I am not doing it in the right order. Any help on this is greatly appreciated!

Result value will reset every month for all resources.

Raw data:

Transformed Data with the expected result:

Thank you,

Apoorva

[marcus_sommer]

Sorting for only one field isn't enough else like hinted above you need to specify all relevant fields in the order by statement, like:

resident X order by Project, Date, Priority;

starting the field-listing with those which should be sorted at first which seems for your example:

resident X order by Month, Ressource, Priority;

You could add as much fields as you need and each of them could be independently sorted descend or ascend - without specifying the direction it would be ascend by default.

In more complex scenarios it would be also possible to combine n sorting-fields in a pre-load and/or applying there any kind of rank/flags within/without (nested) if-loops to support the needed sorting.