12 Replies Latest reply on Nov 14, 2018 6:45 AM by Paul Wachtler

# LOD Calculation

Hello All

 ID Order ID Order Date 12345 1 1-Jan-18 67891 2 2-Jan-18 15678 3 3-Jan-18 65412 4 4-Jan-18 12345 5 5-Jan-18 12345 6 6-Jan-18 15678 7 7-Jan-18 15678 8 8-Feb-18 15678 9 9-Mar-18 67891 10 10-Jan-18 67891 11 11-Jan-18 65412 12 12-Jan-18 65412 13 13-Feb-18 65412 14 14-Mar-18 12345 15 1-Jan-18 12345 16 1-Jan-18 12345 17 1-Feb-18 12345 18 1-Feb-18 12345 19 1-Mar-18 12345 20 1-Mar-18 12345 21 1-Mar-18

i wanna have result about how much id that have orders more than 2 in every month with range 3 months.

regards

Rosy

• ###### 1. Re: LOD Calculation

Hi Rosyaful,

You can do something like this.  I'm going to use a fictional date field called [end date] to mark the end of the three month period that you'd like to specify.  You can make this value anything you'd like and it will make sure to only include order dates within the previous 3 months:

countd(

if [Order Date] >= dateadd('month',[end date],-3) and

{fixed [ID]: count([Order ID])} > 1 then [ID]

)

Let me know if you have any questions.

Best,

Paul

• ###### 2. Re: LOD Calculation

Sorry Paul,

it doesn't work with my case, error calculated when I try your calculation.

regards

Rosy

• ###### 3. Re: LOD Calculation

Hi,

Please find my approach stored in the attached workbook. I created a calculation to get the required count. You can apply relative date filtering to get data for 3 months.

~Tushar

• ###### 4. Re: LOD Calculation

I'm Sorry Tushar, maybe any missunderstanding with my problem explanation.

I wanna know how much ID which have order id more than 2 in every month while 3 month contiuned. in condition order date is updating daily date.

• ###### 5. Re: LOD Calculation

Will you be able to share some image or hand drawing of your final result?

• ###### 6. Re: LOD Calculation

Based on what you posted you have in Jan you have two ids and Mar has 1.

I did not apply 3 month filter on it.

• ###### 7. Re: LOD Calculation

Rosyatul - I need you to at least share a screenshot of the error you're experiencing with my code.  It's very infrequent that you can create a Tableau calculated field without having a copy of the viz.

If you show me what error you're experiencing, I may be able to help you figure it out.

Best,

Paul

• ###### 8. Re: LOD Calculation

This is my experience about that calculation

• ###### 9. Re: LOD Calculation

Dear Tushar

I mean, I wanna result from this picture below...so the result is just 1 ID which have parameter analyst that order ID more than 2 in every months while this period (3 months). but until now I haven't solution calculation to get this result. thank you before

• ###### 10. Re: LOD Calculation

Create a calculated field using the formula below:

{FIXED [ID],MONTH([Order Date]):COUNTD([Order ID])}>2

and { FIXED [ID]:COUNTD(MONTH([Order Date]))}>2

Put this to filters and set to True.

• ###### 11. Re: LOD Calculation

Hi,

Please find the attachment if it is Correct or Helpful for you.

Thanks,

Amar D. Savale

• ###### 12. Re: LOD Calculation

Oh, that's a simple issue to fix.  I forgot to add the word "end" in the calculation.  That's what happens when trying to write a calculation without having access to the workbook or the data

Update the calculation to this:

countd(

if [Order Date] >= dateadd('month',[end date],-3) and

{fixed [ID]: count([Order ID])} > 1 then [ID] end

)