Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
jerryr125
Professor-Ambassador
Professor-Ambassador

FirstSortedValue - KPI - First negative value in a table chart

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

DateForecast Daily usageCumulative UsageForecast Balance on Hand
8/27/20203,100 10,000
8/28/20204,0007,1002,900
8/29/20201,0008,1001,900
8/30/20202,20010,300-300
8/31/20202,50012,800-2,800
9/1/20201,80014,600-4,600
Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

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

View solution in original post

18 Replies
brunobertels
Specialist II
Specialist II

may be this 

firsortedvalue(Date, 

- if( 

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

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

) , Date)

jerryr125
Professor-Ambassador
Professor-Ambassador
Author

getting closer - but still not working.

I changed 'Date' to SALECALENDARDATE

Perhaps a () off somewhere in the formula

thoughts ? Jerry

sunny_talwar

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

View solution in original post

Kushal_Chawda

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

@jerryr125  or try below

firstsortedvalue(SALECALENDARDATE, 

aggr(if( 

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

,SALECALENDARDATE) , 

SALECALENDARDATE))

 

sunny_talwar

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

Kushal_Chawda

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

sunny_talwar

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

jerryr125
Professor-Ambassador
Professor-Ambassador
Author

Hi everyone - thank you for your assistance !

The second one did the trick - appreciate it ! Jerry