6 Replies Latest reply: Nov 5, 2016 5:50 PM by Sunny Talwar RSS

    RangeSum is not ignoring zeros

    Luiz DePaula

      Hello experts,

       

      I have a problem that might be simple but I have not found any solutions in previous posts and actually I saw other people having the same issue. All days with no sales are being displayed in the chart when using rangesum to show cumulative data.

       

      I am using rangesum function on a line chart to display sales by month. The data parameter used is the delivery date, so if we are looking sales for November, we see when the orders came in (creation date), but we restrict the data based on the delivery date. So, for November sales, I have orders being displayed starting in October until mid November.

       

      The function works perfectly to restrict the summation of the data, however the days being displayed in the chart goes until December. No extra value is added, but I cannot restrict the days to show only with data. I have already unchecked "Show Zeros Values" and "Show Null Values". However, when I select a specific month, zeros do not display. So, it means there is a way to get rid of the zeros. The function I am using is below:

       

      If(GetSelectedCount(DeliveryMonth)=0,

           

           rangesum(above(Sum({$<DeliveryDate={">=$(=Monthstart(today()))<=$(=monthend(today()))"}>}Orders),0,rowno())),

          

           rangesum(above(Sum({$<DeliveryDate={">=$(=Monthstart(max(DeliveryDate)))<=$(=monthend(Max(DeliveryDate)))"}>}Orders),0,rowno())))

       

      Any ideas?

       

      Thanks in advance,

       

      LD

        • Re: RangeSum is not ignoring zeros
          Sunny Talwar

          May be you need to add another condition within your if statement? If this doesn't work, would you be able to share a sample of what exactly are you trying to do? May be we can help better using a sample

           

          If(Sum(Orders) > 0,

          If(GetSelectedCount(DeliveryMonth)=0,   

              rangesum(above(Sum({$<DeliveryDate={">=$(=Monthstart(today()))<=$(=monthend(today()))"}>}Orders),0,rowno())),

              rangesum(above(Sum({$<DeliveryDate={">=$(=Monthstart(max(DeliveryDate)))<=$(=monthend(Max(DeliveryDate)))"}>}Orders), 0, RowNo()))))

            • Re: RangeSum is not ignoring zeros
              Luiz DePaula

              Hi Sunny,

               

              One more time you are here to save the day . This condition is simple and almost worked. The only problem is that I have 2 data sets, for 2 different years, and I need to show the data for both years in the same chart.

               

              If I simply use

                   If(Sum(Orders)>0,...) it gives me still the full set of days with zeros because last year we most likely had orders for those days.

              If I use

                   If(Sum({<Year={"$(=year(today()))"}>}Orders),...) it gives the right data set, but when I add last year's data it breaks the current year sales line.

               

              What would you recommend that would work for both time periods?

               

              Thanks again,

               

              LD

              • Re: RangeSum is not ignoring zeros
                Luiz DePaula

                Sunny, it worked with the initial logic you suggested. I had to add a condition for both years, as I have two time frames. Sometimes I see a couple of days with zeros, as last year had sales for those days, however, it is not showing the several days with zeros as it was doing before.

                 

                Thanks again!

                 

                LD