6 Replies Latest reply on Feb 18, 2016 4:31 AM by Shinichiro Murakami

# Counting Months Without Data

I am trying to produce a report that is able to count the number of months with no sales and list it next to the customers name. I am also trying to identify new customers who are defined as customers with sales within the previous 12 months who had no sales between the previous 18 months and previous 12 months. Any help is greatly appreciated!

• ###### 1. Re: Counting Months Without Data

Matthew,

You need to use many LOD s to achieve the goal.  //  Overview: Level of Detail Expressions

[Previous 12 Months]

[Previous 18 Months]

[Max Date in the data]

{fixed :MAX ([Date])}

[Last Sales Date]

{Fixed [Customer #]:max([Date])}

[Previous 12 months Sales]

sum(IF [Date] >=  [Previous 12 Months]

and [Date] <= [Max Date in the data] then [Sales] END)

[Previous 18 months Sales]

sum(IF [Date] >=  [Previous 18 Months]

and [Date] <= [Max Date in the data] then [Sales] END)

[18 month - 12 month]

{fixed [Customer #]:[Previous 12 months Sales]}

-{fixed [Customer #]:[Previous 18 months Sales]}

[New Flag]

if [18 month - 12 month]=0 then "New" else "Existing" end

[No Sales Month]

datediff('month',[Last Sales Date],[Max Date in the data])

I think this works, but could you double check?

Thanks,

Shin

• ###### 2. Re: Counting Months Without Data

Sugoi Murakami San! Thank you VERY much for ALL your help on this! I'm an advanced Excel user trying to convert over to Tableau. LOD's have been something I'm trying to learn and the examples you've been so kind to provide will help a lot. I appreciate all your work here! Domo Arigato Gozaimasu!

P.S. If your going to TC16 I owe you a few beers!

• ###### 3. Re: Counting Months Without Data

Matthew

I have marked Shin's response correct.

Cheers

Mark

• ###### 4. Re: Counting Months Without Data

Matthew,

Thank you for the kind comment with  Japanese !!

I/company don't have money and I'm not sure I can go there or not, but try.

Mark,

Thank you for your consideration, but I'm sometimes confused of the comment.   "I have marked XXX's response correct"

Do you/very limited people have special power to do that ?

Thanks,

Shin

• ###### 5. Re: Counting Months Without Data

Mark,

Thank you for your consideration, but I'm sometimes confused of the comment.   "I have marked XXX's response correct"

Do you/very limited people have special power to do that ?

Thanks,

Shin

In short, yes.

I am a forum ambassador, and as part of that, it means I have moderation privileges and am able to move threads, mark answers, and deal with general moderation.

You can read more about me and my fellow ambassadors here >

and

and finally

There seems to be quite a few of us now

Keep up the good work, and thank you for all you do!

Mark

• ###### 6. Re: Counting Months Without Data

Mark

I didn't know that.

Now I understand.

Thank you for the detail explanation.

Shin