topic Re: Display Date when Balance-on-Hand is less than zero (first occurance) in App Development

Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Tue, 25 Aug 2020 12:04:00 GMT

Hi -

I have the following formula in a KPI :

=MIN(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PSALECALENDARDATE))

This displays the FINAL negative value of the BOH Products over the dates (one year of dates)

I would like to display the actual date when the BOH Product goes from positive to negative.

Thoughts ?

Thanks - Jerry

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

sunny_talwar — Tue, 25 Aug 2020 12:33:47 GMT

May be this, might need to change a little bit, but the idea is to use First Sorted Value function

FirstSortedValue(PSALECALENDARDATE, Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PSALECALENDARDATE), PSALECALENDARDATE))

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Tue, 25 Aug 2020 13:43:18 GMT

HI - unfortunately that did not work 😞 any other thoughts ?

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Wed, 26 Aug 2020 08:42:49 GMT

Hi - any other thoughts ?
- Jerry

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Thu, 27 Aug 2020 15:16:43 GMT

HI - I have a couple tried a couple of variations of the above but no luck 😞

any thoughts ??? Thanks so much - Jerry

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

Kushal_Chawda — Thu, 27 Aug 2020 15:38:45 GMT

Please share a sample data with expected output

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Thu, 27 Aug 2020 16:44:20 GMT

Hi - thank you.

Here is an example of my data below.

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 - hence 08/30/2020.

thoughts ?

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: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Mon, 31 Aug 2020 07:39:38 GMT

Hi any thoughts ? is this possible ? Jerry

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Wed, 02 Sep 2020 13:05:54 GMT

Hi - I am still a little puzzled on this formula. Perhaps a 'FirstSortedValue' and if statement - any thoughts ?

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Thu, 03 Sep 2020 16:36:46 GMT

Hi -

I tried something like this - no luck :

=IF(Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())) < 0,FirstSortedValue(PALENDARDATE, Aggr(($(vBOHProductFinal))-aggr(Rangesum(Above(($(vForeCastProductTotal)),0,RowNo())),PCALENDARDATE), PCALENDARDATE)))

thoughts ?

Re: Display Date when Balance-on-Hand is less than zero (first occurance)

jerryr125 — Wed, 09 Sep 2020 08:37:43 GMT

Hi - I am still stuck on this one - any thoughts / luck ?