1 2 3 34 Replies Latest reply on Jun 7, 2018 12:35 PM by rayvel.lopez Go to original post
• ###### 15. Re: Count each month greater than zero

Can you try the below.

{Fixed Year([Transaction Post Date]), Month([Transaction Post Date]):

Sum(if((DATEDIFF('month', [Transaction Post Date], TODAY()) <13  AND DATEDIFF('month', [Transaction Post Date], TODAY()) >= 1)

AND (Charge Amount) > 0

THEN 1

ELSE 0

END)

}

• ###### 16. Re: Count each month greater than zero

Still showing as one

• ###### 17. Re: Count each month greater than zero

You will need add month,year in the details.

• ###### 18. Re: Count each month greater than zero

I can't add details. I'm going to use this calculation with another calculation

• ###### 19. Re: Count each month greater than zero

See attachment

.

• ###### 20. Re: Count each month greater than zero

How can I upload the workbook? It's saying the workbook is to big

• ###### 21. Re: Count each month greater than zero

Hi Rayvel,

1) Make date from month and year as below:

DATE(DATEPARSE ( "dd-MMMM-yyyy", "1"+"-"+[Month]+"-"+STR([Year]) ))

2) Convert charge amount to number as below

FLOAT(TRIM( SPLIT( [Charge Amount], "\$", 2 ) ))

3) Write calculation for the output

IF DATEDIFF('month',[Date],TODAY())<=12  and  ([Charge Amt]>0 ) then 1 ELSE 0 END

Refer to the attachment for more to get required output as below. Regards,

Sireesha.

• ###### 22. Re: Count each month greater than zero

Hi Rayvel,

Are you looking same!!!

Regards,

Aman

• ###### 23. Re: Count each month greater than zero

Hey Siressha

I'm using version 10.3. Is it possible for you to send the workbook in version 10.3.

Thank you

• ###### 24. Re: Count each month greater than zero

Hey Aman

I'm using version 10.3. Is it possible for you to send the workbook in version 10.3.

Thank you

• ###### 25. Re: Count each month greater than zero

{ FIXED [Number of Records]: COUNT([Charge Amount]>0)}

• ###### 26. Re: Count each month greater than zero

Hi Sireesha

I tried your code and I'm getting a total of 711. Each month there will be multiple payments. I'm looking to get one count for each month.

Thank you

• ###### 27. Re: Count each month greater than zero

you want to use below formula and do the count distinct. Please see attached workbook.

IF DATEDIFF('month',[Date],TODAY())<=12

and

([Charge Amount]>0 )

then [Month] END • ###### 28. Re: Count each month greater than zero

Hi Rayvel,

pls find workbook in version 10.3.

Regards,

Aman

• ###### 29. Re: Count each month greater than zero

Hi Rayvel,

Refer to the attachment in 10.3 and if I understood correctly now, even in case of multiple dates in the data, the count should be 1  if the charge amount for the whole month is greater than 0. In the workbook, I have taken multiple dates within a month to test the calculation.

Below is the calculation used to get the result,

{FIXED MONTH([Date]):MIN(IF DATEDIFF('month',[Date],TODAY())<=12  and

[Charge Amount]>0 then 1 ELSE 0 END)}

Hope it resolves the issue.

Next time, try to be more specific in the question.

Cheers,

Sireesha.

You can attach the workbook by clicking on the Advance editor option on the right top corner. 1 2 3