Numbers of Months (elapsed time) since a month when consecutive zeros are started

gregoryTan

Hi Anaplaners,

 

I would like to figure out numbers of months elapsed since consecutive zeros have started. (please see the attached).

For instance, in the attached file, as sales of product BBB is zero since July 2019(instead of April 2019), I would like to get "4"(months) given current period is Oct 2019. 

Thanks in advance.

 

Regards,

Greg

numbers_of_Months_nonzero_42309.xlsx

DavidSmith

@VIGNESH.M @gregoryTan 

There is, as ever, a much simpler approach

1. Set up a time lookup module that holds the value of the current period.  Format the line item as time period - month

 

2. Create three additional line items in your data module.  We will be utilising a little known technique that allows us to "reset" a cumulate count based on a boolean parameter

Zero Count = IF Data > 0 THEN 0 ELSE 1

Reset? = Zero Count = 0

Cumulate Zero Count = CUMULATE(Zero Count, Reset?)

3. Then you can link this to the summary module using the line item from 1

This allows for data within the run of zeros

 

As a general note, remember that all calculations that only refer to time should be calculated in a time settings module rather than in multi-dimensional modules.

 

E.g. START() <= CURRENTPERIODSTART()

and 

PERIOD(CURRENTPERIODSTART())

 

I hope this hepls

David