topic Re: Latest month with dataset in Integration, Extension & APIs

Latest month with dataset

dennisthemenace — Sat, 16 Nov 2024 01:48:07 GMT

Dear all,

hopefully there is anybody, who can help me with the following:

My aim was to get a KPI with the relative devolopement (%) of revenues from current month 'Jul' compared to previous month 'Jun'. Right now, it looks like that:

EDITOR / Script :

SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';

LET vAktuellerMonat = 'Jul'; //Variable for current month
LET vVorMonat = 'Jun'; // variable for month before

KPI OBJECT:

(
Sum({1<Jahr={'$(=num(year(today())))'},Monat={'$(vAktuellerMonat)'}>}Revenue) //current year, month - revenue
-
Sum({1<Jahr={'$(=num(year(today())))'},Monat={'$(vVorMonat)'}>}Revenue) //current year, prev. month - revenue
)
/
Sum({1<Jahr={'$(=num(year(today())))'},Monat={'$(vVorMonat)'}>}Revenue) //current year, prev. month -revenue

Unfortunately, nothing from the community, that I read was working so far. Something like Month(today()) of course doesn't work out, coz the dataset is related to the upper defined Monthnames.

Thank you very much in advance ! 🙂

Regards

Dennis

Re: Latest month with dataset

tresB — Mon, 27 Jul 2020 13:08:03 GMT

Sorry, could not really follow. What is that you are trying and not working? Could you explain a bit more ? Are you trying to make these current and previous months dynamic?

Re: Latest month with dataset

dennisthemenace — Tue, 28 Jul 2020 05:55:38 GMT

Hi!

Not so easy to explain, but you are right. I want to make a dynamic 'current month' and a 'current month - 1' within the variables now.

In best case, the current month is based on the last ReloadTime().

Re: Latest month with dataset

tresB — Tue, 28 Jul 2020 06:04:21 GMT

You can try using timer_mode argument in today(). For getting the date when the reloaded was finished, you can use: Today(0) or the reloadtime() itself.

Therefore, the variable would look like:

vCurrentMonth=Month(Today(0))

vPrevMonth=Month(Addmonths(Today(0),-1))

Re: Latest month with dataset

dennisthemenace — Tue, 28 Jul 2020 06:35:04 GMT

Good Morning Tresesco,

thanks for your quick response. The solution is unfortunately not working, because (I think) the output from the variables are number values. On the other hand, the months in KPIs are defined by field names like 'Jun' , which is not a number.

Re: Latest month with dataset

tresB — Tue, 28 Jul 2020 07:34:22 GMT

Month() returns a dual value (i.e. - text as well as number). The issue could be somewhere else. If you can share a sample file, we could give a try to investigate and help you.

Re: Latest month with dataset

dennisthemenace — Tue, 28 Jul 2020 11:57:49 GMT

Hi again,

finally I got it work. There must have been a formal mistake first, which made your hint not working. Afterwords, its been an issue of having no data for current month.

So now I am using it as:

LET vAktuellerMonat = Month(Addmonths(Today(),-1));
LET vVorMonat = Month(Addmonths(Today(),-2));