topic Re: Need formula like RangeSum(Above)... in QlikView

Need formula like RangeSum(Above)...

Mon, 15 Feb 2016 13:39:05 GMT

I have encountered a problem with a formula when SR13 were installed on our server

The goal of the formula is to get one day per employee, the one day where more than 50% of damages within a given period is done (finished, paid...)

The simple formula is: Closed/Total, and Closed means closed up until given day (at day nr 4 then calc closed damages from day 0 to day 4).

I have the days in a dimension and used a formula:

rangesum(above(sum([_count_closed_damage]),0,RowNo()))

Aggr( nodistinct count([_count_all_damage]),SKI)

This gives a good calculated percentage but to get only THE ONE I want i used:

IF(rangesum(above(sum([_count_closed_damage]),0,RowNo()))

Aggr( nodistinct count([_count_all_damage]),SKI)

)>=0.5,

if(

(rangesum(above(sum([_count_closed_damage]),1,RowNo()))

Aggr( nodistinct count([_count_all_damage]),SKI)

)<0.5,

rangesum(above(sum([_count_closed_damage]),0,RowNo()))

Aggr( nodistinct count([_count_all_damage]),SKI)

)

This puts a zero in all places but the right one, and then combined with Supress Zero did the job elegantly ( )

BUT in SR13 Supress Zero has unfortunately been banned in this type of formula (Why this has been banned is an answer i have not seen...)

So, I need another way of calculating, and I need help!

I have tried encapsulating the existing formula in a Sum(Aggr()) which enables SupressZero to work as it should, but this only works if load-order is correct.... and that I cannot achive. The load script is complex, and Days is a calculated field...

I have experimentet with Set-analysis (using Rowno, ValueLoop and so on) but as always, Set-analysis is calculated before the chart...

I can not put everything in the load script since the funktionality depends on user-selections

So I desperately need help in finding a way to solve this puzzle!

Re: Need formula like RangeSum(Above)...

swuehl — Mon, 15 Feb 2016 18:22:59 GMT

Your table seems to be working ok also in QV12. I assume an upgrade is not an option?

There might be other expressions possible that return your requested result, but I would need to think about alternatives a bit more.

Getting your load order correct is quite easy, though, just create a table load with the field in question first in the script, before you are loading any other occurences of that field. Then, if you can assume a value range 0 to 1000 days for example, create a table load like

DUMMY:

LOAD RecNo()-1 as [Handläggningsdagar]

AutoGenerate 1000;

Then load your other tables and perform your calculations and transformations.

At the very end, you can drop the DUMMY table, if you want:

DROP TABLE DUMMY;

Even when you drop the table, the initial load order of the field values is preserved.

Now your Sum(Aggr(..., Dim1, Dim2)) should work just fine.

Re: Need formula like RangeSum(Above)...

Tue, 16 Feb 2016 11:52:58 GMT

Great thanks for your input!

Indeed, the solution you suggested worked in all its simplicity! sometimes you dont see the forest for all the trees...!

The upgrade to qv12 is scheduled to early fall so we will se what happens then!

Thanks again!