4 Replies Latest reply on Jan 12, 2017 8:08 AM by Shinichiro Murakami

# Monthly number of new accounts vs. total number of accounts

Hello:

I am having trouble counting for new accounts (unique) by month and then total number of accounts (running total). Where an account first appear in a month and did not exist anywhere in prior history is considered a "new account". And then calculate a running total of all accounts. What I'm able to do is a count distinct on accounts (1st portion), but not the lasttwo.

Message was edited by: Thanh Nguyen

• ###### 1. Re: Monthly number of new accounts vs. total number of accounts

Hi Thanh

Could you attach packaged workbook.

To analyze explain the solution need to create workbook.

To replicate 450 line item by our side is quite troublesome and not time efficient.

Thanks,

Shin

• ###### 3. Re: Monthly number of new accounts vs. total number of accounts

Thanh,

Things like "New Account" are a good application for a Level-of-Detail expression, which allows you to identify whether a given transaction is the first time a transaction has been seen by a particular customer.

First the LOD expression:

[Customer First Transaction Date]

`{ FIXED [Customer ID] : MIN( [Transaction Date] ) }`

Then you can build a calculation to use for your running total:

[New Account]

`IF [Transaction Date] = [Customer First Transaction Date] THEN 1 END`

Of course you can combine these into one calculated field. I just broke them out for clarity.

1 of 1 people found this helpful
• ###### 4. Re: Monthly number of new accounts vs. total number of accounts

Hi Thanh

You can get desired result with using LOD.  Overview: Level of Detail Expressions

[First Order Month by Customer]

if {fixed [Customer Name]:min([Order Date])}= [Order Date] then [Customer Name] end

Then count this field by countd and running sum.

Thanks,

Shin