5 Replies Latest reply on Jul 23, 2018 1:14 PM by Naman Bhardwaj

    Lookup, LOD and DateDiff

    Naman Bhardwaj

      Hi All,

      please help me solving this problem

      in attached tableau workbook i have created calculated field "from_date".. i want to create a dashboard where  for a suborder i want to check the delay in status flow

      so if my order is moving from one status to another status within 6 hours i will say that its within TAT otherwise delayed TAT

      like this there is another condition for moved and not moved

      i think am facing problem while creating "from_date" field

      all other calculations like "calculation 1", "calculation 2"," calculation 3" depends upon this field

      in workbook there are two sheets, one is raw data and other is pivot

      if i copy the cross tab of raw data sheet and paste than into excel and create pivot there it works fine but if i try to do same thing in tableau its not working

      i am struggling with either LOD or aggregate,Non Aggreagte

      for example in pivot sheet from_status "picked" to to_status "Invoiced" is providing the order count value "1873 under moved and beyond TAT"

      but if i just copy the cross tab in a excel and create pivot in excel then it will be actually 39 orders in "Moved and Beyond TAT" and 1838 in "Moved and within TAT"   which is right and what i want

      i hope you understand the problem

      attaching workbook






        • 1. Re: Lookup, LOD and DateDiff
          Naman Bhardwaj

          Dont know if i was able to explain my problem properly or not

          but even after lot of try i am not able to get this done

          waiting for your comments

          • 2. Re: Lookup, LOD and DateDiff
            Deepak Rai

            Problem is there is lot of Description, Can you Cut it down to What you need, then you may make it Visible for action.

            • 3. Re: Lookup, LOD and DateDiff
              Naman Bhardwaj

              Hi Deepak
              in pivot sheet
              there is column " Moved" within that there are two bifurcations "beyond TAT" and "within TAT"

              in those columns data is incorrect
              e.g. from picked to invoiced(higlighted in yellow)  "Moved beyond TAT" value  is 1873 which is not correct
              the value under  "moved beyond TAT" should be 39 and "moved within TAT" should be 1834

              • 4. Re: Lookup, LOD and DateDiff
                David Maning



                I am a bit confused by your description. Please clarify, which diff do you want to analyse? For example, diff between 'picked' and cancelled then diff between picked and invoiced. If so, the calculation is pretty easy.


                COUNTD(IF DATEDIFF('hour', [Sub_Order_Created_At], [To Date]) > 6

                THEN [Order_Id]



                Trust this helps.


                • 5. Re: Lookup, LOD and DateDiff
                  Naman Bhardwaj

                  well..it will be a long description
                  see a order goes through different status e.g. (verified, picked, invoiced,shipped, delivered etc).. i want to create a condition which help me find out that between which two status order is taking more than 6 hours..i have entry and exit time of order in certain status(from_date and to_date)..if my order remains in only one status i will say it "Not Moved" if it goes from one status and recahed to another status i will say it "moved".. now if it has moved depending upon time difference i want to check whether it moved within 6 hrs or not...if yes i will say it Moved within TAT'' if it moved but took more than 6 hours i will say it "Moved but beyond TAT"
                  hope i am able to explain it