topic Re: FirstSortedValue - KPI - First negative value in a table chart in App Development

FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Sat, 16 Nov 2024 01:29:54 GMT

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

Re: FirstSortedValue - KPI - First negative value in a table chart

brunobertels — Thu, 10 Sep 2020 10:05:35 GMT

may be this

firsortedvalue(Date,

- if(

($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)<0,

($(vProductBOHFinal))-aggr(Rangesum(Above(($(vProductForeCastTotal)),0,RowNo())),SALECALENDARDATE)

) , Date)

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Thu, 10 Sep 2020 12:00:56 GMT

getting closer - but still not working.

I changed 'Date' to SALECALENDARDATE

Perhaps a () off somewhere in the formula

thoughts ? Jerry

Re: FirstSortedValue - KPI - First negative value in a table chart

sunny_talwar — Thu, 10 Sep 2020 12:22:38 GMT

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))

Re: FirstSortedValue - KPI - First negative value in a table chart

Kushal_Chawda — Thu, 10 Sep 2020 12:33:58 GMT

@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))

Re: FirstSortedValue - KPI - First negative value in a table chart

Kushal_Chawda — Thu, 10 Sep 2020 13:38:53 GMT

@jerryr125 or try below

Re: FirstSortedValue - KPI - First negative value in a table chart

sunny_talwar — Thu, 10 Sep 2020 15:33:16 GMT

@Kushal_Chawda this will sort based on SALECALENDARDATE, won't it?

Re: FirstSortedValue - KPI - First negative value in a table chart

Kushal_Chawda — Thu, 10 Sep 2020 15:47:28 GMT

@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.

Re: FirstSortedValue - KPI - First negative value in a table chart

sunny_talwar — Thu, 10 Sep 2020 16:01:10 GMT

@Kushal_Chawda Oh because it is an accumulated value... that sounds promising and smart way to do it.

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Thu, 10 Sep 2020 16:25:53 GMT

Hi everyone - thank you for your assistance !

The second one did the trick - appreciate it ! Jerry

Re: FirstSortedValue - KPI - First negative value in a table chart

brunobertels — Fri, 11 Sep 2020 07:43:58 GMT

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

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Wed, 23 Sep 2020 15:10:46 GMT

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 ?

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Mon, 28 Sep 2020 13:35:01 GMT

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

Re: FirstSortedValue - KPI - First negative value in a table chart

sunny_talwar — Mon, 28 Sep 2020 13:36:47 GMT

@jerryr125 How do you define vABCCALC?

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Mon, 28 Sep 2020 13:40:19 GMT

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

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Mon, 28 Sep 2020 14:07:40 GMT

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

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Mon, 28 Sep 2020 14:18:34 GMT

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))

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Mon, 28 Sep 2020 14:26:43 GMT

vABCCALC =

Re: FirstSortedValue - KPI - First negative value in a table chart

jerryr125 — Wed, 30 Sep 2020 14:13:16 GMT

Ok figured it out !

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