topic Re: Depending Date Ranges in QlikView

Depending Date Ranges

Anonymous — Tue, 13 Jun 2017 15:13:20 GMT

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

Re: Depending Date Ranges

Anonymous — Tue, 13 Jun 2017 15:29:17 GMT

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

Re: Depending Date Ranges

Anonymous — Wed, 14 Jun 2017 07:38:26 GMT

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

Re: Depending Date Ranges

prieper — Wed, 14 Jun 2017 10:41:50 GMT

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

Re: Depending Date Ranges

Anonymous — Wed, 14 Jun 2017 13:20:20 GMT

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

Re: Depending Date Ranges

marcus_sommer — Wed, 14 Jun 2017 15:07:19 GMT

You could try the following:

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

- Marcus

Re: Depending Date Ranges

Anonymous — Wed, 14 Jun 2017 15:17:12 GMT

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