Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
num(sum({$<PostingYearMonth = {$(=(PostingYearMonth)-100)}, UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')
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
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
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
You could try the following:
num(sum({<PostingYearMonth = {$(=concat(distinct PostingYearMonth-100, ','))}, UmsatzArt -= {'Plan'}, BusinessYear = >} Amount), '#.###,0')
- Marcus
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