Latest month with dataset

[dennisthemenace]

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

[tresesco]

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?

[dennisthemenace]

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().

[tresesco · Accepted solution]

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

[dennisthemenace]

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. 

[tresesco]

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.

[dennisthemenace]

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));