Sum the differences of several rows in a condition - Qlik Community

Anonymous · ‎2018-01-30

Hey all,

I don't know if it's exactly possible, but I've only got up to a point where I cannot evolve. I have the following data:

cod_solicitacao,cod_tipo,dat_cadastro

26179,10,2016/06/02

26179,4,2016/06/10

26179,10,2016/06/10 **

26179,3,2016/06/16 **

26179,10,2016/06/16

26179,4,2016/06/22

26179,10,2016/06/22 **

26179,3,2016/07/07 **

26179,10,2016/07/11

26179,3,2016/07/11

26179,10,2016/07/27

26179,4,2016/07/27

26179,3,2016/09/22

26179,10,2016/10/20 **

26179,3,2016/11/04 **

26179,3,2017/01/30

26179,3,2017/02/03

26179,10,2017/02/03

26179,3,2017/02/08

26179,4,2017/02/09

26179,10,2017/05/11

26179,4,2017/05/11

26179,4,2017/05/20

26179,10,2017/05/20

26179,3,2017/06/19

26179,4,2017/11/22

26179,10,2017/11/23

26179,3,2017/12/14

26179,10,2018/01/09

26179,10,2018/01/26

And so on... the field cod_solicitacao is the identifier to join to the table with the PK. What we need is: for each rec with cod_tipo = 10, subtract the following record where cod_tipo <> 10 (for instance the ones that I've put ** beside, but for the whole set), and when the last record is = 10, subtract the date from today.

I could only make it work with this function:

=Sum({$<cod_tipo={"<10"}>}dat_cadastro)-Sum({<cod_tipo={"10"}>}dat_cadastro)

BUT only if 1) I have one and only one record after a 10, 2) I have one and only one 10, 3) the table starts with a 10, 4) the table doesn't end with a 10 (which excludes that last condition of what we need, of course).

So: is it really possible to do this with set analysis/if/aggr, or should we try to "fix" the dataset while loading from the database?

sunny_talwar · ‎2018-01-30

You are summing a date field? I am not sure I follow the reason for summing a date field? Also, are you doing this in a chart or text box object?

Anonymous · ‎2018-01-30

I'm summing the number of days between these dates -- I need to know how many days have passed for each 10 until a 3 or a 4. I've tried this in a chart object.