topic Re: Period of previous months in QlikView

Period of previous months

Anonymous — Tue, 24 Jun 2014 16:19:55 GMT

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

Re: Period of previous months

javier_florian — Tue, 24 Jun 2014 16:44:40 GMT

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

Re: Period of previous months

vgutkovsky — Tue, 24 Jun 2014 18:28:02 GMT

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

Re: Period of previous months

Tue, 24 Jun 2014 18:34:03 GMT

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.

Re: Period of previous months

Anonymous — Wed, 25 Jun 2014 11:52:33 GMT

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!

Re: Period of previous months

vgutkovsky — Wed, 25 Jun 2014 15:03:05 GMT

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

Re: Period of previous months

Anonymous — Wed, 25 Jun 2014 16:39:04 GMT

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

Re: Period of previous months

vgutkovsky — Wed, 25 Jun 2014 16:53:16 GMT

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

Re: Period of previous months

Anonymous — Wed, 25 Jun 2014 17:37:30 GMT

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

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

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

Спасибо)

Re: Period of previous months

vgutkovsky — Wed, 25 Jun 2014 18:17:00 GMT

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

Re: Period of previous months

Anonymous — Wed, 25 Jun 2014 18:59:50 GMT

да, это то что я искал

Я так понимаю,эти переменные я должен прописать в скрипте или ?

Можете написать по подробней, что делает переменная vMaxYear?
Есть ли у меня возможность прописать выражения целиком, чтобы не использовать переменные, которые считаются еще отдельным выражением?

Re: Period of previous months

vgutkovsky — Wed, 25 Jun 2014 20:19:46 GMT

You define variables in the variable overview--CTRL+ALT+V. Make sure that they start with an equal sign as I wrote them above.

vMaxYear is used as a control to make sure that the user actually selects a valid year. For example, if I select 2014 and 2015 (today), vMaxYear will return 2014 since March 2015 hasn't happened yet. Likewise, if I select no year, vMaxYear will still return 2014. If I select 2013, however, vMaxYear will return 2013.

I suppose you can do it without variables, but why would you want to? They just make life easier and don't hurt anything in terms of performance. Plus they make it much easier to maintain the application, since each component can be updated separately.

As always, marking correct answers as correct is much appreciated 🙂

Regards,

Vlad