Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

New to Clickview - Need to display backlog graph

Hello all,

I am new to QlickView and am trying to display a graph based on a backlog of work orders over time. I have an Oracle SQL statement that selects the data  but cannot get the same result from Qlickview.  I really dont grasp the concept of sets and expressions and how to use them properly. I can plot either Created or Completed on seperate graphs over a period of time, but cant figgure how to combine and end up with the proper result set.

Here is what I was trying to do. Any ideas/ suggestions? Is this possible in Qlickview?

     Backlog.jpgBacklog 1.jpg

Below is the SQL to select data from Oracle. It displays the backlog for the past 12 months, from this I can generate the graphs above. (I'm not an expert in SQL, its slow but does work). Note I must read the whole data set to compute the opening backlog in the last 12 months.

select * from

(SELECT "Period",

     "Created" , "Completed", 
      Sum("Backlog") OVER(order by "Period" RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)  AS "Backlog"
from
(

select "Period",
"Created" , "Completed",

"Created" - "Completed" as "Backlog"
from
(select
"Period",
"Created",
  (Select count(*) from work_orders where to_char("Actual Finish",'YY-MM') = "Period" and "Stat" in('Complete')) as "Completed"
from (
  select
to_char( "Created on",'YY-MM') As "Period",
sum(1) as "Created"
          from work_orders
group by to_char( sap."Created on",'YY-MM')
order by   to_char( sap."Created on",'YY-MM')
)
))

) where "Period" >= to_char(sysdate -366,'YY-MM')

Regards,

Robert

0 Replies