1 2 3 Previous Next 34 Replies Latest reply on Jun 7, 2018 12:35 PM by rayvel.lopez

# Count each month greater than zero

Hi

I'm looking for LOD to get a count for the last 12 months if the charges are greater than zero.

So far this is what I have

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

then

Thank you

Example:

 Year Month Charge Amount Count 2017 June \$0.00 0 2017 July \$0.00 0 2017 August \$0.00 0 2017 September \$0.00 0 2017 October \$10.00 1 2017 November \$150.00 1 2017 December \$175.00 1 2018 January \$200.00 1 2018 February \$15.00 1 2018 March \$30.00 1 2018 April \$50.00 1 2018 May \$75.00 1 \$705.00 8
• ###### 1. Re: Count each month greater than zero

Rayvel,

I'm just taking a shot in the dark at this since, but you may want to just add a secondary clauses to your if/then statement. Something like:

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

AND COUNT(x) > 0

THEN SUM(Charge Amount)

END

-Wesley

If this post assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution.  Thank you.

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

And if you want the total of those sums, wrap that whole logic in WINDOW_SUM()

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

Hi,

Can you try the below:

Filter by top 12 Month, Year filter.(Max) in Top tab.

Create a calculated field as

Cal= If Sum(Charge Amount) >0 Then 1 else 0 end

Count as {Fixed Year(Date), Month(Date): Sum(Cal)}

Regards

Sohan

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

Hey

The data doesn't have count column. I'm looking for LOD to count it for me.  Don't what to display any of the column in the sheet just total count of 8

 Year Month Charge Amount 2017 June \$0.00 2017 July \$0.00 2017 August \$0.00 2017 September \$0.00 2017 October \$10.00 2017 November \$150.00 2017 December \$175.00 2018 January \$200.00 2018 February \$15.00 2018 March \$30.00 2018 April \$50.00 2018 May \$75.00 \$705.00
• ###### 5. Re: Count each month greater than zero

Hey Sohan

I can't use the filters since I'm going to use other measures in the same sheet. I would like for this to be in LOD calculation.

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

Ah, ok, then maybe this will get us closer:

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

AND SUM(Charge Amount) > 0

THEN 1

ELSE 0

END

You can then either wrap this in a SUM or wait until you drag it into the view to pick the aggregation.

-Wesley

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

Hey Wesley

I'm getting calculation error cannot mix aggregate and non-aggregate arguments with this function.

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

Try this

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

AND SUM(Charge Amount) > 0

THEN 1

ELSE 0

END

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

Hey Sohan

The calculation works only if I have Year and month in rows. I'm looking to get the total without having to put year an month in the rows.

Thank  you

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

Try using year and month in details instead of rows.

Also as mentioned you don't want to use filter and do this calculation, I don't think this would work.

Try to wrap this if condition in the LOD.

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

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

AND SUM(Charge Amount) > 0

THEN 1

ELSE 0

END

}

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

Yeah, it didn't work

Getting the following error level of detail expressions cannot contain table calculations or the ATTR function

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

First calculation:

{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)

THEN (Charge Amount)

ELSE 0

END)

}

Second calculation:

if SUM(First calculation) > 0

THEN 1

ELSE 0

END

The Second calculation should give you the result.

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

It's giving me a total of 1

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

It's time to upload a workbook.

1 2 3 Previous Next