6 Replies Latest reply: Jun 14, 2017 11:17 AM by Sven Stachelhaus RSS

    Depending Date Ranges

    Sven Stachelhaus

      Hi folks,

       

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

       

      Business Year
      Posting Year MonthAmount
      16 / 17201705100
      16 / 17201704100
      15 / 16201605100
      15 / 16201604100
      14 / 15201505100
      14 / 15201504100

       

      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
          Alluraiah Allu

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

          • Re: Depending Date Ranges
            Sven Stachelhaus

            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
                Peter Rieper

                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
                    Sven Stachelhaus

                    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

                  You could try the following:

                   

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

                   

                  - Marcus

                    • Re: Depending Date Ranges
                      Sven Stachelhaus

                      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