last value in table

[AlexGourdet]

Hi,

I need to insert a "tachometer" that shows the last month value.

I need to extract the last value on a table (in the following image the value in the first row and in the "2022-05" column)

I tried to make a table and set te when condition "date=max(date)" but it doesn't work. How can i solve this?

thank you!

[AlexGourdet]

Thank you for visiting SAP Community to get answers to your questions.

Since you're asking a question here for the first time, I'd like to recommend you with the following steps so you can get the most out of your community membership:

• Please see: https://community.sap.com/resources/questions-and-answers as this resource page provides tips for preparing questions that draw responses from our members
• Consider to take our Q&A tutorial at: https://developers.sap.com/tutorials/community-qa.html. as that will help you when submitting questions to the community.

I also recommend that you include a profile picture. By personalizing your profile, you encourage readers to respond: https://developers.sap.com/tutorials/community-profile.html.

I hope you find this advice useful, and we're happy to have you as part of SAP Community!

All the best,
-Alex

[nscheaffer]

Can you provide a mockup in Excel of what you would like it to look like?

[former_member1139600]

Sure,

here attached an image with an example.

I would like to be able to extrapolate only the value of the last month in te first table.

image.png

[nscheaffer]

Start by creating a variable (Var Max Date) to get your maximum date. Take note of the "In Block" portion. You need that.

=Max([Year]) In Block

Then create another variable (Var Last Value) to compare each date to the maximum date.

=If([Year] = [Var Max Date]; 1; 0)

Since you changed table type (crosstab to vertical) I will show examples of both. You can then add a filter on Var Last Value = 1.

An object does not need to be on a report to filter on it. If you want to leave it on the table, you can do so and hide that column if you don't want to see it.

Noel

[former_member1139600]

Thank you Noel.

What do you mean with "Take note of the "In Block" portion. You need that"? I don't understand this part

[former_member1139600]

update: I tried and it works well.

but the problem is when I try to convert the table in a speedometer it losts the filter and I have again some random value

Is there a way to set up a sort of filter directly in the formula (maybe using in or foreach)?

[nscheaffer]

I do not have much experience with charts so I cannot really help you there. How about creating another query that only gets the data for the date you want in your speedometer chart?

So how do you always get last month's date dynamically? The simplest way is with free-hand SQL statement. I am most familiar with SQL Server so here is what it would look like for me...

SELECT FORMAT(DATEADD(MONTH, -1, GETDATE()), 'yyyy-MM') AS [LastYearMonth]

Here is the dbfiddle.

You then you use that LastYearMonth value in your query to get the data from just last month. With this approach you don't have to worry about finding the last value within the report; that is all you will have.

Does that work for you?

[former_member1139600]

I cannot use SQL free-hand (internal policy).

I'll try to find out how to make a query with only last month value.

Thank you very much for your help!

[nscheaffer]

Here is another possibilty. Can you do specify "Use a custom query script"?

You just have to create a universe query with one object of the same data type you want to return in your custom query and replace the universe generated query with your "free-hand SQL" custom query. You should be good to go. Unless the custom query script is also disabled in your system.

Noel

[former_member1139600]

Unfortunately also the script viewer is disabled

[former_member1139600]

Here an explanation of what's happening:

I have a months column (yyyy-MM) with relatives OEE data, and it's correct. When I set the data with the formula "Max([data])" it shows correctly the last month, in this case May, but with a random value, not the value related to may.

Here two screenshot explaining the situation.

This one is the table with the correct values

This one is the last month value, vith a random value instead of the correct may's value (in this case 0.39 based on the previous table)

How can I solve this? I need to see the last month in a separate table (than I will convert this table in a speedometer Chart).

thank you!