RangeSum is not ignoring zeros

[luizcdepaula]

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

[sunny_talwar · Accepted solution]

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()))))

[luizcdepaula]

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

[sunny_talwar]

If you add last year's data it breaks? Would you be able to explain this with an example to help me understand better?

[luizcdepaula]

The first chart is Current year only. It is still bringing some zeros, but not for the whole year as it was doing before. Below is the chart when I add Last Year data in red. The picture does not show everything, but in the sliding bar under the chart you will see that it shows days for several days of the year with zeros and the CY data (in blue) has broken lines.

Pic 1 is good

Pic 2 is not working and has broken lines for CY in blue

[luizcdepaula]

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

[sunny_talwar]

Suppppper, I am glad you made it to work finally