15 Replies Latest reply: Sep 12, 2016 7:16 AM by Anat Dagan RSS

    Get rangesum to ignore date filter on bank balance running totals

    Anat Dagan

      Hi,

       

      I created a bar chart showing running totals for daily accumulating bank balances of 5 different bank accounts where I can filter for one or more bank names and see their running totals per day using this expression:

       

      rangesum(Above(Sum([incom])-Sum([outcom]),0,RowNo()))

       

      As long as When i filter by bank the running totals show the correct balances but if i filter by date the running totals only take into consideration the picked dates , therefore showing incorrect bank balances.

       

      how do i get the rangesum calculation to ignore any date selection so the running totals still show the correct daily accumulating balance, but still allow bank name selections?

       

      I understand Set Analysis can be of help here but I am not able to find the right way to use it.

       

      Thanks in advance

        • Re: Get rangesum to ignore date filter on bank balance running totals
          Sunny Talwar

          May be this if you just want to ignore Date.

           

          RangeSum(Above(Sum({<Date>}[incom])-Sum({<Date>}[outcom]), 0, RowNo()))

           

          In case you want to ignore Date, Month, Year, you can try this

           

          RangeSum(Above(Sum({<Date, Year, Month>}[incom])-Sum({<Date, Year, Month>}[outcom]), 0, RowNo()))

            • Re: Get rangesum to ignore date filter on bank balance running totals
              Anat Dagan

              Thanks Sunny! I tried the first option and it worked great when I used Date field as a filter.

               

              I have now set up a master item called MonthDate to filter my other sheet charts by months (Rather than by days). My MonthDate master item looks like this:

               

              date(Monthend(Date),'MM-YYYY')

               

              However,  I can't seem to replace  {<Date>} with {<MonthDate>} in the above rangesum formula. Is it possible to add master items to formulas (i don't see the master items on the drop down list as i start to type their names)?

              how should i adjust my rangesum formula to ignore selections made on my MonthDate filter?

               

               

              And another question if i may , now that the running totals remain correct, is it possible that although the calculation is made on all dates, the display will still highlight / move to the chosen dates?

              for example if i have data from August to 2016 to Jan 2018 and I filter the sheet for September 2017, is it possible for the bar chart to highlight or move to show the dates in September17 rather than remain on the first dates  from August16?

               

              (apologies for the newbie questions....gotta start somewhere )

                • Re: Get rangesum to ignore date filter on bank balance running totals
                  Sunny Talwar

                  I am very new to Qlik Sense and don't really know if master item can be ignored or not, but why don't you create MonthDate in the script?

                  Date(MonthEnd(Date), 'MM-YYYY') as MonthDate

                   

                  and now you should be able to ignore MonthDate by adding it to your set analysis

                   

                  For second question, I am not 100% sure what you looking to do, would you be able to provide a sample qvf to clarify what is your expectation?

                  • Re: Get rangesum to ignore date filter on bank balance running totals
                    Stefan Wühl

                    1) You can only use fields from your model in a set modifier on the left of the equal sign, not calculated dimensions or master items that are not just a simple field.

                     

                    As far as I see, using your master item and making selections in it should basically select in Date field, so the set expression that clears Date should work with your master item, too.

                     

                    2) You may add some more complexity to your chart to filter on the selected month or high light the bar, but it may be easier to use e.g. an AsOf table approach for your accumulation over time:

                    The As-Of Table

                • Re: Get rangesum to ignore date filter on bank balance running totals
                  Anat Dagan

                  Stefan, Sunny - thanks for trying to help me. much appreciated!

                   

                  I am (trying to) attach a QVF to explain my issue better.

                   

                  Sunny - I didn't create the MM YYYY in data load editor because editing the script disables the data manager and I really want data manager to work for me as i tend to get lost in scripting .

                  And when i tried to add a calculated field in the data preparation stage of data loading, the option was greyed out:

                   

                  Stefan - I don't know why but setting the exclusion condition on the  "date" field does not work when filtering by MMYYYY. see the qvf file.

                  the as of table seems like a very good solution but i am afraid it's too complex for my capabilities at this point. i hope there's a simpler solution.