Period of previous months

[Anonymous]

Good day!

My problem is with getting data by previous six month from some month which is getted from variable vMonth from input field

I'd tried this expression, but it doesn't work

({<Month={"<=$(=vMonth)>=$(=vMonth-6)"}>}[Volume l.])

[javier_florian]

Hi Folk,

How do you declare vMonth? When I work with previous months, I used follow expression:

- Month selected by user:

Year_Month={$(vMaxYearMonth)]

          Where vMaxYearMonth=Num(Max(YearMonth))

- Same month previous year:

Year_Month={$(vPrevMaxYearMonth)}

          Where vPrevMaxYearMonth=Num(Max(YearMonth)-100)

- Year Today:

Year={$(vMaxYear)}, Month={"<=$(=Num(Max(Month)))"}

-JFlorian

[vgutkovsky]

Andrey,

The issue may be the field that you are trying to use (Month). If your month is provided in format MMM-YYYY or something similar, then you can't use that for a > and < comparison. I would suggest switching it to a comparison that uses lowest-level dates in the set analysis. Something like: sum({<Date={">=$(vDateStart)<=$(vDateEnd)"}>}[Volume I.])

These 2 variables would be new variables that you should create. The definition would be something like:

vDateStart

=addmonths(monthstart(vMonth),-6)

vDateEnd

=monthend(vMonth)

Regards,

Vlad

[#4]

Hi,

If you use Month in your set analysis you need specify text value, is better if you use a date value or a MonthYear value, in the next example I use a field MonthYear declared in my calendar "MonthName(myDate) as MonthYear"

Expresion that I use:

sum({<MonthYear = {'$(=MonthName(AddMonths(Max(myDate),-6)))'}>}Sales)

Best Regards.

[Anonymous]

thank you for answer,
now my problem is more simple...

i have field with years (...2013,2014)

field with monthName(....Apr,May,Jun)

what must i write in script to make some flag, which will get dynamically period from March this year to October last year?

Thank you!

[vgutkovsky]

Andrey, I'm not sure I understand. If the months you want to flag in the script are static, what is "dynamic"? Perhaps you mean how can you write the UI expression I mentioned above without a single field for MonthYear? It might be easier if you mention exactly (1) what filters values users select, and (2) what the expected result is.

Vlad

[Anonymous]

Vlad, of course, month field is static, but not years. In this year this flag must get me a period october'13-March'14

Next year it will get me period october'14-march'15...etc

now i want to make such flag in script(not in expression, which i described it in header)

Thank you for help

[vgutkovsky]

Sorry, I still don't understand what you're trying to achieve. What are users selecting? What are you displaying and in what medium? Feel free to answer in Russian, if it helps.

Vlad

[Anonymous]

В отчете в выражении у меня очень много фрагментов формулы, которая считает средние продажи за последние 6 месяцев, начиная с марта 2014:

sum({<ГодМесяц={201403,201402,201401,201312,201311,201310}>}Volume)/6

Для того, чтобы не менять все вручную заново в следующем году, мне нужно создать флаг, который сам автоматически будет брать период с марта текущего года по октябрь прошлого.
То есть, в следующем году он возьмет март.15-октябрь.14

Спасибо)

[vgutkovsky · Accepted solution]

I see. So, basically, if the user selects 2014, you want to display the average for October 2013 - March 2014. In that case, use the following expression and change the variables as follows:

sum({<ГодМесяц={">=$(vDateStart)<=$(vDateEnd)")>}Volume)/6

vDateStart

=(vMaxYear-1) & '10'

vDateEnd

=vMaxYear & '03'

vMaxYear

=max({<Месяц={3}>}Год)

There's no need to create any flag in the script. This expression assumes that the user is, in fact, selecting a year. If no year is selected, the maximum available year (that has a March month) in the data set will be used. Is that what you were looking for?

Cheers,

Vlad