9 Replies Latest reply on Feb 11, 2014 12:08 AM by pradeepbangarusamy

# Calculation Issue

1. How do I calculate FYTD order count when I have a date filter is current month.

2. How do remove from my denominator  if atleast one time they tested from their visit to last 12 months.

• ###### 1. Re: Calculation Issue

Let me explain little more one each one

How do I calculate FYTD order count when I have a date filter is current month?

I am not sure how to calculate FYTD order count when the user selected current month as filter. is it possible to

do in tableau or we need to pre-calculate from DB and populate.

How to remove from my denominator  if atleast one time they tested from their visit to last 12 months.

 Data Date tested bbb 1/2/2012 1 aaa 1/10/2012 1 aaa 1/1/2013 cccc 1/23/2013 1 bbb 2/10/2013 ddd 3/10/2013 1 ddd 5/15/2013 eee 5/20/2013 1 ddd 5/28/2013 1

for example: this is my sample data.

Formula:

how many people tested on the selected period / (how many people visited on that period - how many people not tested with in those period but tested with in 12 months from their visit).

• ###### 2. Re: Calculation Issue

Hi Pradeep, I may have misunderstood but it looks to me like you are trying to provide an estimate of year to date orders based on previous values, if this is so, I've got around this in the past using a window_average in this instance on your tested field, then adding this to dates in the future to find the estimate or forecast.

My post on this may be of use with the calcs http://community.tableau.com/thread/127516

• ###### 3. Re: Calculation Issue

No , I am not looking forecasting.

Let me explain step by step

I have three field in my dataset .

1. person

2. isTested

3. visitdate

Dateset

aaa,yes,2013-09-01

bbb,yes,2013-09-01

ccc,no,2013-09-01

ccc,yes,2012-09-01

ddd,no,2013-09-02

ddd,yes,2012-08-01

eee,no,2013-09-01

aaa,no,2013-09-02

fff,no,2013-09-10

ggg,yes,2011-01-01

if I give the date filter to current month then I need to show the below values

1. Total # of person within selected period

output (aaa,bbb,ccc,ddd,eee,fff  ) so total # of person = 6

2. Person who tested within selected period.

output : (aaa,bbb) so total # of person = 2

3. Person who tested within prior 12 months from their visitdate but not tested in selected period

output : (ccc) so total # of person = 1

4. Person who not tested within prior 12 months from their visitdate as well as not tested in selected period

output : (ddd,eee,fff) so total # of person = 3

5. tested % is  2/(3+2)

(i.e ) point #2 / ( point #4 +  point #2)

• ###### 4. Re: Calculation Issue

Hi Pradeep, thanks for this, I now have a clearer understanding of what you are trying to achieve; I'm at work now and shall get a response over to you this evening if this ok with you.

In the meantime, it may be of help if you are able to provide a workbook, even if it running from superstore sales just so I can ensure I am following your mock-up.

• ###### 5. Re: Calculation Issue

I don't have a workbook since am not sure how to achieve this . I have a dataset like above and when I try to use two calendar parameter to filter the dataset then I can't go back 12 months prior period since I have filtered dataset based on my date parameter.

• ###### 6. Re: Calculation Issue

Apologies Pradeep, didn't manage to get a look at this last night; its not two parameters you need, just an if and a sum case statement by the looks of things.

I shall take a proper look tonight but to get you on your way I suspect you shall need two calcs (to make things easier) then use something like:

<Not Taken>

If Attr([Date]) >= [Date Parameter]-365 And Attr(Date) <=[Date Parameter] Then

Sum(Case When [Taken] = 'No' Then 1 Else 0 End)

End

<Taken>

If Attr([Date]) = [Date Parameter] Then

Sum(Case When [Taken] = 'Yes' Then 1 Else 0 End)

End

And then the actual calc:

<Percent>

Sum([Taken])/(Sum([Taken]) + Sum([NotTaken]))

And a final conditional filter dropped onto filtering and set to 'In'

<Date Filter>

If [Date] >= [Date Parameter]-365 And [Date] <= [Date Parameter] Then 'In' Else 'Out' End

Like I said, I shall try again this evening but tell me how you get on.

• ###### 7. Re: Calculation Issue

Hi Pradeep, did this work for you?

• ###### 8. Re: Calculation Issue

I think this will work for me but I didn't tried yet.

• ###### 9. Re: Re: Calculation Issue

as you mentioned, I have tried but it's not working. I have a data name called "ddd" . this person visited on 05/28/2013 and not tested but this person tested 3/10/2013 so my expectation is, this person also count when I have May month filter.