Last 6 sales but dates are not sequential

[Anonymous]

Hi

I have data as below and want to show last 6 transdate sales in bar char. Since my dates are not sequential i cannot put Max(date)-5.

I have applied formula sum({<StatDt={">=$(=Max(StatDt,5))"}>}Sales) but when i make selection in StatDt field i am getting error message that "No Data to Display" in chart

Please suggest new formula in front end to fix.

Note (i can fix it by script in using autonumber function)

StatDt	Sales
2/29/2016	65
3/12/2016	5
3/26/2016	10
4/5/2016	15
4/12/2016	20
4/28/2016	25
5/1/2016	30
5/10/2016	35
5/29/2016	40
6/20/2016	45
7/26/2016	50
11/10/2016	55
2/25/2017	60
5/10/2017	70
5/15/2017	75
5/30/2017	80

Regards

ramu

[boorgura]

if you make a selection in StartDt --> then there would be no valid value for max(StartDt, 5) - unless you select 5 or more dates.

If you want top 5, irrespective of selections - then use max({1} StartDt, 5)

[sunny_talwar]

Try this:

Sum({<StatDt={">=$(=Max({<StatDt = {'<=$(=Max(StatDt))'}>}StatDt,5))<=$(=Max(StatDt))"}>}Sales)

[Anonymous]

Thanks Sunny it is working. Can you please explain how it is evaluating in QV. especially this portion Max({<StatDt = {'<=$(=Max(StatDt))'}>}StatDt,5) why did you mention {'<=

[effinty2112]

Hi Ramu,

If you want to look at data for the last 6 dates loaded the expression in this table will do the trick.

cheers

Andrew

StatDt	if(FieldIndex('StatDt',StatDt)>FieldValueCount('StatDt')-6,Sum(Sales))
26/07/2016	50
10/11/2016	55
25/02/2017	60
10/05/2017	70
15/05/2017	75
30/05/2017	80

[sunny_talwar]

The issue is that as soon as you select a single date, the Max(Date, 5) become null because there is only one date and max(date, 5) is looking for the 5th maximum date. By adding {<StatDt = {'<=$(=Max(StatDt))'}>}, I told the expression to look at all the date before the max date selected and find the max(date, 5) from that list.

Does this make sense?