# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
See why BI users voted Qlik #1 in 11 categories. GET REPORT
cancel
Showing results for
Did you mean:
Highlighted
Contributor III

## Return a specific Break Even Date from Table

Hi guys,

I am trying to calculate break even in a chart and I want to return just the first value when my Column A > Column B.

if(if(Rangesum(above(\$(vRDERDist),0,rowno()))>Rangesum(above(\$(vRDERCost),0,rowno())),1,0)=1,Date(min(Data_PeriodEnd)),null())

This is the expression I have in the chart at the moment that gives me every date if Column A > Column B.

How do I change the formula if I want to return the first data where Column A > Column B ?

Labels (5)

• ### rangesum

10 Replies
Highlighted
MVP

May be something like this

```If(

If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0) = 1

and

Above(If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0)) = 0

, Date(Min(Data_PeriodEnd)), Null())```

This might not be the best way to do it, but without seeing a app to test with, this is the best I can think of.

Highlighted
Contributor III

It works in the chart but what if I want to show that single Date in a chart like the one below with the companies.

Do I have to use aggregation around every rangesum ?

 Asset Name Date Break Even Company 1 Company 2 Company 3 Company 4

```If(

If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0) = 1

and

Above(If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0)) = 0

, Date(Min(Data_PeriodEnd)), Null())```

Thanks

Michele

Highlighted
MVP

@mikecherry wrote:

It works in the chart but what if I want to show that single Date in a chart like the one below with the companies.

It works in the chart, but it doesn't work in the chart? What do you mean? Why does it work and why does it not work?

Highlighted
Contributor III

If I copy the same expression into a chart showing Companies (like in my prior post) rather than dates it won't work anymore as I guess I have to use aggregation formulas around my expressions.

Highlighted
MVP

Try this

```Aggr(
If(

If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0) = 1

and

Above(If(RangeSum(Above(\$(vRDERDist), 0, RowNo())) > RangeSum(Above(\$(vRDERCost), 0, RowNo())), 1, 0)) = 0

, Date(Min(Data_PeriodEnd)), Null())

, Company, Data_PeriodEnd)```
Highlighted
Contributor III

Looks like is working. I'll do a little bit of testing and let you know.

Thanks a lot

Highlighted
MVP

Sounds good 🙂

Highlighted
Contributor

Quick question on the previous solution re: breakeven date calculation.

How can I apply a similar approach if my variable is a calculated?

Ex: If need formulas to derive the equivalents of "vRDERDist" and "vRDERCost", do I add the smaller formula into the longer string that you shared?

Highlighted
Contributor

I created variables and successfully inserted them into the break even formula.

Unfortunately, the formula is returning the dates when values "broke-even" and became negative, as opposed to the dates that they "broke even" in the process of turning from negative to positive.

How can I address that in the formula?