Hi Jose,

If you run COUNTOF in the playground you’ll see it removes the month dimension.

You can do 'Billsmonth Spread'aBY count: Month] to get what you want

Hi Jose,
COUNTOF will remove all dimensions. If you want to count cells, you can however use BY COUNT to get to your result.

Hope this helps!

That worked well with the  BY count: Month] !  Thanks!  

If I want to do a formula that counts the previous months also? like I want to count how many are not blank but where blank in the previous 3 months, to identify new users.

On excel would be an easy formula like this =COUNTIFS(CK$36:CK$6500;"<>0";CJ$36:CJ$6500;0;CI$36:CI$6500;0)

 

Thank you very much in advance!

Jose

I did the following formula and it seemed to work:

 

IF('Billsmonth Spread''SELECT: 'Data Hub'::Month-3]=0 AND 'Billsmonth Spread''SELECT: 'Data Hub'::Month-2]=0 AND'Billsmonth Spread''SELECT: 'Data Hub'::Month-1]=0 AND 'Billsmonth Spread''SELECT: 'Data Hub'::Month]<>0,1,0)

Hi Jose,
You’re on the right track!

Here’s what I’ve built based on your formula. It’s different metrics but the logic should be the same.

The MOVINGSUM helps get the added value for last three months, so a blank value should mean a blank value on each of the last three months.

Please also note that IF does not need a third argument, you can just leave it blank which will be more efficient.

Hello Francois;

It doesnt work well the formula of ISDEFINED because I  0s instead of blanks.

I tried adapting it but it did not work:

IF(ISBLANK(MOVINGSUM('Billsmonth Spread',3),SELECT:'Data Hub'::Month-1])AND 'Billsmonth Spread'aSELECT:'Data Hub'::Month]<>0;1)

 

I got Syntax error: cannot validate formula

I don’t get why you added a SELECT in that last part - it’s probably the source of the error.

I think your formula should be

IF(MOVINGSUM(ABS('Billsmonth Spread'),3))SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)

I’ve added ABS to make sure two consecutive values don’t cancel out.

Hi Francois!

 

I tried doing the formula as you suggested, but I still get Syntax error: cannot validate formula

 

Thank you!!

I didn’t read the formula completely… You have a semicolon instead of just a comma.
IF(MOVINGSUM(ABS('Billsmonth Spread'),3))SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0;1)

IF(MOVINGSUM(ABS('Billsmonth Spread'),3))SELECT:'Data Hub'::Month-1] = 0 AND 'Billsmonth Spread' > 0,1)

Thank you Francois!  It worked well.