Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi - I thought I would try again and re-post:
The data is in a table chart.
The Forecast Balance on Hand is a master measure
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)
so I tried this :
=MIN(($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)) and it returns the last value of the table chart below:
-4600
I want to actually return the first date in which the forecast balance is negative.
Therefore, the KPI should display : 08/30/2020
thoughts ?
thanks - Jerry
Date | Forecast Daily usage | Cumulative Usage | Forecast Balance on Hand |
8/27/2020 | 3,100 | 10,000 | |
8/28/2020 | 4,000 | 7,100 | 2,900 |
8/29/2020 | 1,000 | 8,100 | 1,900 |
8/30/2020 | 2,200 | 10,300 | -300 |
8/31/2020 | 2,500 | 12,800 | -2,800 |
9/1/2020 | 1,800 | 14,600 | -4,600 |
May be this
FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE) > 0, -1E10, $(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE)), SALECALENDARDATE))
or may be this to simplify it a little
FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())) > 0, -1E10, $(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo()))), SALECALENDARDATE))
may be this
firsortedvalue(Date,
- if(
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)<0,
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)
) , Date)
getting closer - but still not working.
I changed 'Date' to SALECALENDARDATE
Perhaps a () off somewhere in the formula
thoughts ? Jerry
May be this
FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE) > 0, -1E10, $(vProductBOHFinal)-Aggr(Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())), SALECALENDARDATE)), SALECALENDARDATE))
or may be this to simplify it a little
FirstSortedValue(SALECALENDARDATE, -Aggr(
If($(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo())) > 0, -1E10, $(vProductBOHFinal)-Rangesum(Above(($(vProductForeCastTotal)), 0, RowNo()))), SALECALENDARDATE))
@jerryr125 also try below
firstsortedvalue(SALECALENDARDATE,
aggr(if(
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),(SALECALENDARDATE,(NUMERIC,ASCENDING)))<0,
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),(SALECALENDARDATE,(NUMERIC,ASCENDING)))
,SALECALENDARDATE) , SALECALENDARDATE))
@jerryr125 or try below
firstsortedvalue(SALECALENDARDATE,
aggr(if(
($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),(SALECALENDARDATE,(NUMERIC,ASCENDING)))<0,
,SALECALENDARDATE) ,
SALECALENDARDATE))
@Kushal_Chawda this will sort based on SALECALENDARDATE, won't it?
@sunny_talwar Yes. Basically first to calculated correct cumulative figures with sorting date and then once identified negative from that just assign dates and find earlier date.
@Kushal_Chawda Oh because it is an accumulated value... that sounds promising and smart way to do it.
Hi everyone - thank you for your assistance !
The second one did the trick - appreciate it ! Jerry