Counting total number of consecutive data points (based on date)

Hello,


I'm just wondering if anyone can help me out on counting consecutive points?


I've attached a sample of data.


My data is split into quarters of the year, but I have the start and end date columns to these quarters.


What I'm trying to achieve is a count for each Store the number of consecutive data points each has. Any ideas would be appreciated.


Thanks in advance!


Sample.xlsx

I'm not sure what you mean by 'consecutive', or how you want to display these counts.


My initial thought is to put another column on the end called 'count' with '1' in each row.

Then you get the sum of that column but split it (series/datapoints) by the store Ids.


Does that work for you?

Not quite.

I have the data per store split into quarters (like in the Excel attached above)

E.g.

Store 1

2016 Q1 = 50 bikes sold

2016 Q2 = 60 bikes sold


Store 2

2016 Q1 = 50 bikes sold

2016 Q2 = no data submitted

2016 Q3 = 70 bikes sold


Store 3

2016 Q1 = 50 bikes sold

2016 Q2 = 60 bikes sold

2016 Q3 = 70 bikes sold


I want to be able to see which stores have submitted consecutive quarters of data and how many consecutive data points they submitted data.


E.g.

Store 1 = 2 consecutive quarters submitted

Store 2 = 1 quarter submitted

Store 3 = 3 consecutive quarters submitted

Hey Daniel,


I think I have a solution that may work for you, if you are planning to just view these results within a single year at a time.


I started off by creating a Metric Set that uses you StoreId and StartDate on Rows, and DataPointValue on Measure. I then made sure to add the Quarter level of my Time Dimension on to StartDate, as I will need to utilize Period over Periods for this:


Afterwards I added the following formula to my Metric Set, counting each time a Store made sales:

if($DataPointValue$ !=0)

return 1;

else return 0;


Make sure to set Null Replacement rules on your DataPointValue, so that Full Nulls are replaced with Zeros. This way we can properly see where breaks after taking place.

I then added 3 period over periods on my count formula, going back 1, 2, and 3 quarter, respectively:


Make sure each Period over Period Measure is then set to return Source Nulls as Zero:


I then added the following formula to count each rows longest, consecutive streak of quarterly sales:

var count = 0;

var max = 0;

if($Count$ != 0){

count++;

max = count;

}

else{

max = count;

count = 0;

}


if($Count PoP-1Quarter$ != 0){

count++;

if(count>max)

max = count;

}

else{

max = count;

count = 0;

}

if($Count PoP-2Quarter$ != 0){

count++;

if(count>max)

max = count;

}

else{

max = count;

count = 0;

}

if($Count PoP-3Quarter$ != 0){

count++;

if(count>max)

max = count;

}

else{

max = count;

count = 0;

}


return max;


I then can add a final formula to this Metric Set, to point out the largest value returned for each of my StoreIds in the previous calculation:

TOP($Count Expression$ ,$StoreId$, $StartDate$ )


Now I have the largest streak being associated with each store. And I can use a formula visualization to just get those values displayed on my Metric Set. If I add this Metric Set to a new Dashboard, I can define my formula visualization as such:

AVG($table1.Metric Set 1.Count Expression TOP Expression$ ,$table1.Metric Set 1.StoreId$ )


This will give me the average of my maximum streak values, per StoreId, and since they are all equal it will just give the expected results:

Image title


You may need to make some changes to the formula in order to return the specific number that you are looking for.


Hope this helps!