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

    Calculation Issue

    pradeepbangarusamy

      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
          pradeepbangarusamy

          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.

                                                              

          DataDatetested
          bbb1/2/20121
          aaa1/10/20121
          aaa1/1/2013
          cccc1/23/20131
          bbb2/10/2013
          ddd3/10/20131
          ddd5/15/2013
          eee5/20/20131
          ddd5/28/20131

           

          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
            Steve Martin

            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
              pradeepbangarusamy

              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
                Steve Martin

                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
                  pradeepbangarusamy

                  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
                    Steve Martin

                    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
                      Steve Martin

                      Hi Pradeep, did this work for you?

                      • 8. Re: Calculation Issue
                        pradeepbangarusamy


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

                        • 9. Re: Re: Calculation Issue
                          pradeepbangarusamy

                          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.