Different values depending on different date periods

[johnan]

Hi best community!

I have a table that contains a value from a date to a date. I just want to show that value for the current period.

FromDate	Value
2025-08-03	224
2024-08-03	403,2
2023-08-03	582,4
2022-08-03	761,6
2021-08-03	940,8
2020-08-03	1120

So in this case i just wanna show 940,8 (FromDate 2021-08-03), and when the current date are >= 2022-08-03 i just want to show the value 761,6 and so on 🙂

Any ideas?
Would it be best if I could get it in the script right away?

Br

[vchuprina]

Hi,

Add a valid flag in the script and then use it in set analysis:

DATA:
LOAD * Inline [
FromDate, Value
2025-08-03,     224
2024-08-03,     403
2023-08-03,     582
2022-08-03,     761
2021-08-03,     940
2020-08-03,     1120
];

ValigFlag:
Left Join (DATA)
LOAD
     FromDate,
     1 AS ValidFlag
Where NumOfDate = 1
;
LOAD
     FromDate,
     AutoNumber(FromDate) AS NumOfDate
Resident DATA
Where FromDate < Today()
Order By FromDate DESC;

SUM({<ValidFlag = {1}>}Value)

Regards,

Vitalii

[johnan]

Thanx, it's work when i have one value (i added Asset below), how can i se if there is more data in same table.
I have added Asset:

DATA:
LOAD * Inline [
Asset,FromDate, Value
111,2025-08-03, 224
111,2024-08-03, 403
111,2023-08-03, 582
111,2022-08-03, 761
111,2021-08-03, 940
111,2020-08-03, 1120
222,2019-08-03, 345
222,2020-08-03, 167
222,2024-08-03, 174
];

ValigFlag:
Left Join (DATA)
LOAD
FromDate,
1 AS ValidFlag
Where NumOfDate = 1
;
LOAD
FromDate,
AutoNumber(FromDate) AS NumOfDate
Resident DATA
Where FromDate < Today()
Order By FromDate DESC;

[vchuprina · Accepted solution]

Hi, 

Try this, it returns separate Valid date for each asset

DATA:
LOAD * Inline [
Asset,FromDate, Value
111,2025-08-03, 224
111,2024-08-03, 403
111,2023-08-03, 582
111,2022-08-03, 761
111,2021-08-03, 940
111,2020-08-03, 1120
222,2019-08-03, 345
222,2020-08-03, 167
222,2024-08-03, 174
];

ValigFlag:
Left Join (DATA)
LOAD
     Asset,
     FromDate,
     1 AS ValidFlag
Where NumOfDate = 1
;
LOAD
     Asset,
     FromDate,
     AutoNumber(FromDate, Asset) AS NumOfDate
Resident DATA
Where FromDate < Today()
Order By Asset, FromDate DESC;

Result:

Regards,

Vitalii

[johnan]

@vchuprina , you are a hero 🙂

[vchuprina]

@johnan , welcome🙂