How to latest 15 dates in bar chart

[shruthibk]

Deal All,

I have a daily balance information which is not continuous, i have to show latest 15 dates balance on bar chart.how to find these 15dates (the balance is not available for all the dates like for weekends some holidays also on some weekdays it is not available) so i have to show whatevr the latest 15dates available ?

also, the 15days are we have a reference date if the reference date is selected  7 days after the reference date and 7 dates before the rerernce date to be shown

for example if reference date is 03/15/2024 ( balance available for 03/01/2024,03/03/2024,03/05/2024,03/08/2024,03/10/2024,03/11/2024,03/15/2024,03/18/2024,03/20/2024,03/22/2024,03/28/2024,03/30/2024,04/01/2024,/04/05/2024)

above mentioned dates balance to be shown on bar chart

any guidance is appreciable ,thank you

Regards,

Shruthi

[marksouzacosta]

Hi @shruthibk,

To start, create an index to your dates, like the example below. That will allow you to easily handle Dates:

TempBalance:
LOAD * INLINE [
Date, Balance
03/15/2024, 245
03/01/2024, 125
03/03/2024, 3320
03/05/2024, 420
03/08/2024, 500
03/10/2024, 100
03/11/2024, 10
03/18/2024, 88
03/20/2024, 785
03/22/2024, 46
03/28/2024, 546
03/30/2024, 1234
04/01/2024, 789
04/05/2024, 457
];

Balance:
LOAD 
    AutoNumber(Date,'DateIndex') AS DateIndex, 
    Date, 
    Balance
RESIDENT
	TempBalance
ORDER BY
	Date
;

DROP TABLE TempBalance;

Now write your chart expression like the one below:

Sum({<Date =, DateIndex = {">=$(=Max(DateIndex)-7)<=$(=Max(DateIndex)+7)"}>}Balance)

Note 1: You can adjust the >= or <= to fit correctly the ranges you need.

Note 2: One interesting addition is to change 7 for a variable and allow the users to set the desired range of interval using for instance, a slider.

Note 3: if you don't select any date, the script above will take the highest available date. The same behavior happens when you select multiple dates.