FirstSortedValue - KPI - First negative value in a table chart

[jerryr125]

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

[brunobertels]

Hi Sunny 

just for my knowledge what is this in the expression : 

-1E10

i understand it is right value for the if statement , but what is the use of this value -1E10 ? 

thanks a lot 

[jerryr125]

Hi again and help! 

My formula has changed.

So therefore this KPI logic works

=Date(FirstSortedValue(RCALENDARDATE, -Aggr( If($(vCBOHMFinal)-Rangesum(Above(($(vFCastMTotal)), 0, RowNo())) > 0, -1E10, $(vCBOHMFinal)-Rangesum(Above(($(vCFCastMTotal)), 0, RowNo()))), RCALENDARDATE)),'MM/DD/YYYY')

The IF Statement now changed to this :

IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),
($(vCBOHMFINAL))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCFMTOTAL),$(vDEFMTOTAL))),0,RowNo())),RCALENDARDATE),
($(vCBOHMFINAL))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCFMTOTAL),$(vDEFMTOTAL))),0,RowNo())),RCALENDARDATE))

How to change the formula to reflect the new IF Statement logic ?

[jerryr125]

HI again - 

My formula changed to a simple variable :

vABCCALC

How do I change the formula -? 

I tried this and no luck 

=Date(FirstSortedValue(RCALENDARDATE, -Aggr( If(-Rangesum(Above($(vABCCALC), 0, RowNo())) > 0, -1E10, $(vABCCALC), 0, RowNo()))), RCALENDARDATE)),'MM/DD/YYYY')

Any thoughts ? Jerry

[sunny_talwar]

@jerryr125 How do you define vABCCALC?

[jerryr125]

It is a long formula which is a variable that does give me the correct value (numeric).

So therefore, I simply need to display the related RCALENDARDATE in which the first time the vABCCALC is negative.

vABCCALC is calculated per date - which works 🙂 

any thoughts ? Jerry

[jerryr125]

Hi again - 

Simple enough - vABCCALC the numeric value. The first occurrence of the numeric value I need to know the related RCALENDATE date.

thoughts ? Jerry

[jerryr125]

IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),
($(vCBOHMFinal))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCForeCastMTotal),$(vCDeFaultMTotal))),0,RowNo())),RCALENDARDATE),
($(vCBOHMFinal))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCForeCastMTotal),$(vCDeFaultMTotal))),0,RowNo())),RCALENDARDATE))

[jerryr125]

vABCCALC = 

IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),
($(vCBOHMFinal))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCForeCastMTotal),$(vCDeFaultMTotal))),0,RowNo())),RCALENDARDATE),
($(vCBOHMFinal))-aggr(Rangesum(Above((IF(Num#(Date(RCALENDARDATE,'YYYYMMDD'),'0') > $(vForecastDateStartNumeric),$(vCForeCastMTotal),$(vCDeFaultMTotal))),0,RowNo())),RCALENDARDATE)

[jerryr125]

Ok figured it out !

=Date(FirstSortedValue(RCALENDARDATE, -Aggr( If(($(vABCCALC)) > 0, -1E10, $(vABCCALC)), RCALENDARDATE)),'MM/DD/YYYY')