Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
johnan
Creator III
Creator III

Different values depending on different date periods

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

Labels (2)
1 Solution

Accepted Solutions
vchuprina
Specialist
Specialist

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:

vchuprina_0-1652946616934.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").

View solution in original post

5 Replies
vchuprina
Specialist
Specialist

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)

vchuprina_0-1652942941272.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
johnan
Creator III
Creator III
Author

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

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:

vchuprina_0-1652946616934.png

 

Regards,

Vitalii

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").
johnan
Creator III
Creator III
Author

@vchuprina , you are a hero 🙂

vchuprina
Specialist
Specialist

@johnan , welcome🙂

Press LIKE if the given solution helps to solve the problem.
If it's possible please mark correct answers as "solutions" (you can mark up to 3 "solutions").