Depending Date Ranges

[Anonymous]

Hi folks,

I have another issue here, I have a table with values like this

Business Year	Posting Year Month	Amount
16 / 17	201705	100
16 / 17	201704	100
15 / 16	201605	100
15 / 16	201604	100
14 / 15	201505	100
14 / 15	201504	100

I have one listbox what represents the business years and one for the posting months. Thanks to the forum here I have already a dependency between the date ranges, when I select one month of a certain business year I get the amount of the month from the previous business year summed up. What I would like to achieve now is the effect when users select more than one month in a business year the months from the previous year will automatically also be selected and the amounts get summed up as well.

Here is a screenshot of the two listboxes, the amounts of these selections will be calculated in a table chart.

Here is my current formular to sum the amount from the previous business year based of the selected month

num(sum({<PostingYearMonth = {$(=(PostingYearMonth)-100)}, UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')

With only one selected month it's working very well but with more then one i get only zero amounts. I already know the problem is this part {$(=(PostingYearMonth)-100)}, it subtracts 100 from the selected posting year month value but with two selection it does not work. Maybe there is a possibilty to alter my set analysis to get it work?

Every idea is highly appreciated.

Many thanks.

Kind regards,

Sven

[Anonymous]

num(sum({$<PostingYearMonth = {$(=(PostingYearMonth)-100)}, UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')

[Anonymous]

Hi Alluraiah,

thanks for this quick reply. Unfortunatley it does not work, the behaviour ist like before. When I selected more as one month I get zero amounts.

Kind regards,

Sven

[prieper]

Would propose to use a field with rolling monthes, i.e.

YEAR(MyDate) * 12 + MONTH(MyDate)     AS RollingMonth.

Doing so, you may bypass any formatting issues.

This is clearly a number and you may refer to any Timespan (below will sum last 12 monthes, depending the selected period)

SUM({$< RollingMonth = {">= $(MAX(RollingMonth) -12)"}>} Amount)

HTH Peter

[Anonymous]

Hi Peter,

actually the field posting year month is already a calculated rolling month, it is coming from the orignal date of the invoice. When I use your suggestion I get the total for a business year but this is also only working when I only select one posting year month value in my list box. Somehow it must be possilbe to use a similar expression with e.g. two selected posting year month values, I think the problem is that only one selection will be recognized with the current expressions.

Kind regards,

Sven

[marcus_sommer]

You could try the following:

num(sum({<PostingYearMonth = {$(=concat(distinct PostingYearMonth-100, ','))}, UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')

- Marcus

[Anonymous · Accepted solution]

Hi Marcus,

thanks for the reply but in the meantime I found the solution.

num(sum({<PostingYearMonth = P({$}PostingYearMonthVJ), UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')

I have adapted the generic formular found on this site Qlik Tips: AND Mode in Set Analysis and calculated the field PostingYearMonthVJ in the load script with "PostingYearMonth - 100 AS PostingYearMonthVJ" and voila it works like a dream now. 🙂

To be honest I need to get more familiar with set analysis and operators like $,P,1,etc. in QlikView, it still confuses me.

Kind regards,

Sven