When we are doing sum of last 6 months. if any month is having 0 value, that month is skipping and doing sum of another month.

[Charan1]

(sum(aggr(rangesum(above((sum({<status={'Y'},year=>}sales)),0,6)),add_month_year)))

Ex:-

Suppose if we are doing sum of last 6 months. If any month is having 0 then that month is considering this formula. Kindly suggest any other expression/formula. it has to calculate last 6 months from 201907 to 201902 but it is calculating till 201901 because 201906 skipping (because it is having 0).

chart :- Pivot

201901	65
201902	117
201903	165
201904	203
201905	74
201906	0
201907	3

Thanks in advance.

[rubenmarin · Accepted solution]

Hi, do you have a calendar with all months? if you don't have the YearMonth 201906 it will be skipped. So having a Calendar table it's a good practice to have all dates and months loaded.

If you have the calendar maybe it's being skipped beacuse you have set the option to supress zero values, it's an option on the add-ons section of the pivot table

[Charan1]

Hi Rubenmarin, 

Thanks for your response. 

This is causing when we do apply some filters. If there is no sales for that month value is 0(zero). It is not doing sum for that month. In add ons i have selected check box to do sum 0 values as well. Is there any other option to do sum of the last 6 months if any month is having 0 sales. 

Thanks in advance. 

[rubenmarin]

Hi, you can try adding set analysis to limit data to 6 months, ie. if you have a date field:

(sum(dateField={">=$(=Date(AddMonths(Max(dateField),-6)))"} aggr(rangesum(above((sum({<status={'Y'},year=,dateField={">=$(=Date(AddMonths(Max(dateField),-6)))"}>}sales)),0,6)),add_month_year)))