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:
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!