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.
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).
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
No , I am not looking forecasting.
Let me explain step by step
I have three field in my dataset .
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)
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.
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.
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:
If Attr([Date]) >= [Date Parameter]-365 And Attr(Date) <=[Date Parameter] Then
Sum(Case When [Taken] = 'No' Then 1 Else 0 End)
If Attr([Date]) = [Date Parameter] Then
Sum(Case When [Taken] = 'Yes' Then 1 Else 0 End)
And then the actual calc:
Sum([Taken])/(Sum([Taken]) + Sum([NotTaken]))
And a final conditional filter dropped onto filtering and set to 'In'
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.
Hi Pradeep, did this work for you?
I think this will work for me but I didn't tried yet.
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.
Book2.twbx 9.2 KB