2 Replies Latest reply on Nov 24, 2016 1:10 AM by Tobias Kupke

    How can I show a historical development of a "recent" KPI? Join a virtual time axis?

    Tobias Kupke

      Simplified I have a table with orders that looks like this:

      IDorder_date
      processing_datedelivered_date
      11.1.16 05:591.1.16 07:301.1.16 07:45
      21.1.16 06:591.1.16 07:30null
      31.1.16 07:59nullnull

       

       

      So if I want to know "How many orders are still open (need to be processed)" -> I just check the conditions (order_date not null and processing_date is null) and get the result "1" (id 3)

      Same procedure for "How many orders are still not delivered (but processed)" -> I just check the conditions (processing_date not null and delivered_date is null) and get the result "1" (id 2)

       

      But how can I show a historical development? I want to get a table like that:

      1.1.16 06:00 - 1 open order - 0 processed_not_delivered

      1.1.16 07:00 - 2 open orders - 0 processed_not_delivered

      1.1.16 08:00 - 1 open order - 1 processed_not_delivered

       

      In SQL I think i would know how to do it with a manual generated table with all dates and hours and i would left join subselects with the conditions for each hour (e.g. something like: .....where processing_date < temp_date and delivered_date > temp_date OR delivered_date is null)

       

      I don't have an idea how to generate a manual time axis in Tableau and join the subselects, do you have any ideas? I could use the order_date as time axis but I would need to check for all rows then to get the sum of open orders, so something like a self-join?

       

      Kind regards,

      Tobias

        • 1. Re: How can I show a historical development of a "recent" KPI? Join a virtual time axis?
          Amit Narkar

          TimeLine.png

          This is the closest what I could try with existing data

          • 2. Re: How can I show a historical development of a "recent" KPI? Join a virtual time axis?
            Tobias Kupke

            Thx for your thoughts Amit, but this is not quite the thing I was looking for since it doesn't take into account the timestamps of the changes for delivered and processed.

             

            I thought a lot about it and finally found a cool sql-function and managed to do it through custom sql:

             

            select generate_series

            , count(SO.id) as "open orders"

            , avg(t1."delivered_not_packed") as "delivered_not_packed"

            from generate_series('2016-11-01 00:00'::timestamp, now(), '1 hours')

             

             

            LEFT OUTER JOIN service_orders as SO ON

            SO.timestamp < generate_series

            AND

            (SO.delivered_at > generate_series OR SO.delivered_at is null)

             

             

            Left Outer Join

            (

            select generate_series as "generate_series_2"

            , count(SO2.id) as "delivered_not_printed"

            from generate_series('2016-11-01 00:00'::timestamp, now(), '1 hours')

             

             

            LEFT OUTER JOIN service_orders as SO2 ON

            SO2.delivered_at < generate_series

            AND

            (SO2.packed_at > generate_series OR SO2.packed_at is null)

             

             

            group by 1

            order by generate_series asc

            ) as t1 ON generate_series = generate_series_2

             

             

            group by 1

            order by generate_series asc

             

             

             

            I simplified the query a little bit but the basic thinking is, that I create a timeseries through the function "generate_series" and left outer join the count of ids for the matching conditions. I don't know why it is much slower to do multiple left outer joins the same way but it is much faster to do a second left outer join on a subselect. Somehow it works anyways! :-)