Help needed with lastValue

[orutra11]

Help needed with the use of lastValue (and firstValue).

I have a simple dataset that has the number of customers by segment and date, and I want to extract the current number of clients by segment and date. I thought:

lastValue(customers, [date ASC], [segment])

would do it, but it does not.

Data looks like:
date | segment | customers
2022-08-14 | A | 1000
2022-08-14 | B | 100
2022-08-14 | C | 10
2022-08-15 | A | 1050
2022-08-15 | B | 110
2022-08-15 | C | 11
2022-08-16 | A | 1078
2022-08-16 | B | 120
2022-08-16 | C | 9

Here’s the table I need to plot in Quicksight to visualize the result of the lastValue:

I need to add “customers” to the rows for it not throwing an error.

Any help is appreciated!
Thanks!

[Tejal_Gohil]

Hi,
You can add “customers” to row and then hide the column like below.

Here is final result look like:

I hope this helps!

Thanks,
Tejal Gohil

[orutra11]

Thanks for your response Tejal!

The result I want to achieve is something more like a lastOver ordered by Date partitioned by Segment (actually what lastValue is supposed to do) where I can get the last value for each segment and show it in a KPI PRE_FILTER

[Max]

Have you looked into doing a top / bottom filter based on your condition.

To get the most recent date I would create a calculated field that takes the difference from now until your date.

time_diff = dateDiff({date},now(),‘DD’)

Then filter your kpi to be the top 1 date based on this newly time_diff calculated field.

Let me know if that helps!