Calculated Measures Subtracting Data from Different Months

[Ganjos]

Hi,

We are currently facing a problem with two Excel files that contain the same measures but yield different results. I want to create a calculated measure that subtracts "dotacoes" for February 2024 (202402) from "dotacoes" for January 2024 (202401). However, I am unable to access the measure "dotacoes" for both February 2024 (202402) and January 2024 (202401) in order to perform this subtraction.

Could you assist me in resolving this issue?

Thanks in advance!

GAnjos

[radinator]

Hi, can you please provide some of your code you were trying to do so far? Also could you clarify how exactly you are trying to access the data from the excel file? Do you want to do the calculation in ABAP accessing a locally stored Excel file or are you doing them in the report before exporting the data to excel? Kind regards

[Ganjos]

i get no value at all

[radinator]

So the value returned by the calculation is just zero, right?

[Ganjos]

i did this code to test just subtract:

[Ganjos]

RESTRICT([Previsoes_corrigidas_acumuladas],[d/Date].[h/YM] = "202402") - RESTRICT([Previsoes_corrigidas_acumuladas] ,[d/Date].[h/YM] = "202401")

[Ganjos]

I mean it is literally empty; it does not return 0.

[radinator]

I suggest testing first IF you can actually extract data with the way you want to do it. Like check first if the data extraction of RESTRICT([Previsoes_corrigidas_acumuladas],[d/Date].[h/YM] = "202402") actually yields any data. If this is not the case your problem is most likely a wrong use of the exctraction method.

[Ganjos]

t does not return anything. I tried what I thought was the solution, but it didn’t work at all. Do you have any ideas?

[radinator]

Since I am not too familiar with SAC, I suggest you to look out for some sort of documentation about how to actually extract data from a excel file in SAC. Your problem is NOT that your way/code doesn't work as you posted. Your problem is that you (most likely) use the WRONG code to do the task. If you manage to extract *some* data you will be able to fit together the calculation 🙂

[Ganjos]

I will investigate, thanks for your time 🙂

[Ganjos]

Hi,

Thank you for your previous answer.

I have an Excel file that was provided to me, and I have analyzed it and built the model. However, the data is accumulated, and I want to perform calculations to derive the monthly values. For example:

• For January 2024 (202401) -> 200
• For February 2024 (202402) -> 400 - 200
• For March 2024 (202403) -> 700 - 400

I want to access the value of 400 from February 2024 (202402) and subtract 200 from January 2024 (202401).

I attempted to create nested IF statements like this:

if([d/date]=202402,[dotacoes]-[dotacoes],if([d/date]=202402,[dotacoes]-[dotacoes],if(.....))

IF([d/Date] = 202402, [dotacoes] - [dotacoes], IF([d/Date] = 202401, [dotacoes] - [dotacoes], IF(...)))

However, I am unable to access the values of "dotacoes" for 202402 and 202401 to perform these subtractions.

Thank you!

[radinator]

Hi again, for me it's not entirely clear how exactly you access the excel file. May you add some code you already made?

[radinator]

Additonally it would be very helpfull what the current "error" is. Do you get any value extracted or do you just get "no value" at all. Like the value '0'? Kind regards

[Ganjos]

I am using SAP Analytics Cloud to import the file. I haven't written any code

[radinator]

If you apply one of the calculations, do you an actual error messsage or do you just get a zero value?