Table : Keep only lines with min value

[sarafamiglietti]

Hello,

I have this table :

And I want to keep in this table only the first line where CA is bigger than Costs for each week (ie yellow lines).

Expected Table :

Please see attached qlikview document.

I tried with min(), aggr(), firstsortedvalue() functions but I did not manage to get the good results.

Can someone helps me?

Thanks in advance.

Regards

Sara

[sunny_talwar]

Try this

Min(Aggr(if(RangeSum(Above(sum(ca),0,rowno()))>RangeSum(Above(sum(costs),0,rowno())),(delay)), week, delay))

[sarafamiglietti]

Hello and thanks for your answer.

Indeed, It seems that this formula works in my example.

But when I try to integrate this formula in my qlikview documents (which is little more complex), I have not the good results.

Aggr() function distorts the calculation of RangeSum(Above(sum(ca),0,rowno()))>RangeSum(Above(sum(costs),0,rowno()))

Because aggregation is also applied on RangeSum(Above(sum(ca),0,rowno())) and  RangeSum(Above(sum(costs),0,rowno())) instead of only delay.

I don't know how to solve this.

Regards

[sunny_talwar]

Do you have QlikView 12 or above? If you do, you can use The sortable Aggr function is finally here! to sort the order of your Aggr() function. If you don't have QlikView 12, you will need to fix the sorting in the script to make this work

[sarafamiglietti]

Unfortunately, I don't have QLikview 12 right now.

On which dimension do I need to sort in order to make this work?

Thanks.

[sunny_talwar]

Delay in ascending order.... to do this... you can simply do this at the start of the script

TempDelayTable

LOAD RowNo() as Delay

AutoGenerate 100;

and at the very end of the script

DROP Table TempDelayTable;

[sarafamiglietti]

Hello,

I can't apply this sort because in my script I have delay in week and delay in month.

My example is only on week but I need to apply this formula on month too.

So I can't sort by delay_week AND delay_month at same time.

It seems impossible to solve.

Any other idea to help me?

Thanks and regards

[sunny_talwar]

Don't really understand what you mean? Why can't you sort by delay_week and delay_month at the same time? Would you be able to elaborate?

[sarafamiglietti]

If I sort by delay_week and delay_month in the script I will have something like this:

So delay_month is not sorted

[sunny_talwar]

But where did delay_month and delay_week came from? Weren't we only dealing with delay? Can you please provide a better explanation of your problem with a sample which demonstrate what you really have? Because the lesser you give me, the more time it will take us to resolve this