Adjust denominator for average calculation dynamically

Hi Team,

Let's say there is a dataset for sales of cars for a year (Monthwise) as shown below. The idea is to generate Average column dynamically. The Average column needs to be found in such a way that if there are 2 or more than 2 months have no sale, then that month needs to considered as inactive. Therefore while counting the delimiter on taking average, we need to discard the inactive months. Ex: In row 1 the denominator is 10. Can someone help me with this logic?

Car	2018-1	2018-2	2018-3	2018-4	2018-5	2018-6	2018-7	2018-8	2018-9	2018-10	2018-11	2018-12	Average
X	0	0	10	20	30	10	30	40	20	50	5	0	21.5
Y	10	0	0	0	10	5	6	0	0	5	0	0	7.2
Z	0	0	0	0	0	0	0	0	0	0	20	10	15
A	0	0	0	130	0	0	0	0	0	0	0	0	130
B	0	0	0	0	0	0	0	0	0	0	15	0	7.5

Datasets

Output

Tips and Tricks

Parse

Optimization

Accepted answers

mceleavey

Hi @jaimonsk ,

I've applied that bit of scope creep 😉

Obviously now your averages are different as you are increasing the denominator value for the calc.

dynamic denominator.yxmd

mceleavey

no problem.gif

All comments

mceleavey

@jaimonsk ,

I assume you meant 9 for row 1, not 10.

I built this using a bit of transpose and crosstab magic:

Hope this helps,

dynamic denominator.yxmd

danilang

hi @jaimonsk

There's no single function to do this, since you have the 2-or-more criteria, but you can calculate this yourself

Transpose the values and use a multi-row tool to look for consecutive 0's. Mark those rows with 0 and the all the rest with 1. Sum the values and the marks and use the marks as the denominator.

Dan

AdjustDenom.yxmd

jaimonsk

Hi @mceleavey , @danilang Thanks for the reply. The earlier example i attached was wrong with the criteria i mentioned. I have updated the dataset accordingly and added a <bold> for every numbers which would be in scope. So for first row and last row. The zero at the end would need to get counted in denominator selection. which would alter the average calculation done earlier.
ie, the cell would be considered inactive only if there are 2 or more than 2 continuous cells are zero. In the mentioned case the zero would then need to get counted as active..
Sorry for the confusion!

mceleavey

Hi @jaimonsk ,

I've applied that bit of scope creep 😉

Obviously now your averages are different as you are increasing the denominator value for the calc.

dynamic denominator.yxmd

Quick Links

This months top contributors

hhaigh1 280

FláviaB 262

JORGE4900 235

caltang 177

DanM 167