To convert SQL Query into Expression for Bar Chart

[ankurakash]

Hi,

I am trying to find a approach to write chart level expression based on below given SQL query.

SELECT COUNT(OBJ.ENTER_DT), ROUND(ABS(ENTER_DT - CLOSE_DT)/30,2) AVG_MONTHS, TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY') MONTH

FROM OBJECT OBJ

WHERE OBJ.CLOSE_DT < :P_DATE

AND CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'

AND OBJ.ENTER_DT BETWEEN :P_DATE_FROM AND :P_DATE_TO;

Request your help to convert the sql query into a chart level expression.

Regards..

[maxgro]

read the data from your db

QlikTable:

SELECT

ENTER_DT,

CLOSE_DT

FROM OBJECT OBJ

WHERE

CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'

then add  a bar chart

dimension     month(CLOSE_DT) & '-' & year(CLOSE_DT)

expression     avg(fabs(ENTER_DT - CLOSE_DT))

[ankurakash]

Dear Krishna,

Thanks for the step by step solution. Your solution is helping me to come close to the desired result.

I would request your guidelines in one of the issue I am facing in getting the data correctly(in sequence) for my report.

The scenario is: The SQL query for which I have created the QVD also contains the Group By Clause where field "CLOSE_DT" is first converted to date and then to character using TO_CHAR function - Syntax posted below:

SELECT COUNT(OBJ.ENTER_DT), ROUND(ABS(ENTER_DT - CLOSE_DT)/30,2) AVG_MONTHS, TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY') MONTH

FROM OBJECT OBJ

WHERE OBJ.CLOSE_DT < :P_DATE

AND CLOSE_DT BETWEEN '01-JAN-2014' AND '30-SEP-2014'

AND OBJ.ENTER_DT BETWEEN :P_DATE_FROM AND :P_DATE_TO;

GROUP BY TO_CHAR((TO_DATE(CLOSE_DT)),'MON-YYYY')

Now, when I create the report taking "MONTH" as Dimension, the sequence of month is coming in alphabetical order i.e. they are not coming in sequence.

Look forward for your response.

Regards..

[krishna20]

Dear Akash,

Please attach a sample file, which the query loaded as QVD .

Regards

Krishna

[ankurakash]

Dear Krishna,

The sample data and attachment is shared as below:

1) I have a field "CLOSE_DT" under table "OBJ" on which Master Calendar is created based on the below given field in both the tables:

               Year(CLOSE_DT) & '-' & Month(CLOSE_DT) as Key,

Please consider anything after double forward slash "//" as the comment area

Master Calendar has following fields:

Key   // On which table OBJ is connected

Year // On On which table CLOSEOBJ_QUERY is connected  (Creating Synthetic Key)

Month // On which table CLOSEOBJ_QUERY is connected (Creating Synthetic Key) - Image shared above point2):

Quarter

2) Here, I created the report as per your suggestion: I generated a QVD based on the below given query.

CLOSEOBJ_QUERY:

SELECT SUM(ROUND(ABS(ENTER_DT-CLOSE_DT)/30,2))/COUNT(*) AVG_MONTHS,

TO_CHAR((TO_DATE(CLOSE_DT)),'MON') Month,TO_CHAR((TO_DATE(CLOSE_DT)),'YYYY') Year

FROM OBJ

WHERE CLOSE_DT IS NOT NULL

GROUP BY TO_CHAR((TO_DATE(CLOSE_DT)),'MON'),TO_CHAR((TO_DATE(CLOSE_DT)),'YYYY');

store CLOSEOBJ_QUERY into [$(path)\CLOSEOBJ_QUERY.QVD];

DROP TABLE  CLOSEOBJ_QUERY;

3) At Script level, I called the newly created table:

CLOSEOBJ_QUERY:

LOAD AVG_MONTHS,

     MONTH as Month,    // Renaming it to Month so Master Calendar can be connected

     YEAR as Year    // Renaming it to Year so Master Calendar can be connected

FROM

[..\Data\QVD\CLOSEOBJ_QUERY.QVD]

(qvd);

//Here in the above script I have requirement of both the fields - Month and Year as Dimension for my report generation.

4) At report level, I have tried the following:

Line Chart Properties ->

Dimension -> Month; Year

Expression -> =SUM(AVG_MONTHS)

5) I get the desired result in my report but face two issues also depicted in the attached image:

a) Master Calendar - month is redundant and out of order  // It is concatenating no. of months in the "Month" section in sheet area.

b) I am not getting proper sequence of month while depicting the line chart. Image attached.

Request your guidance in resolving this issue.

Regards..

[ankurakash]

Hi Ram,

Thanks for responding. I am getting close to the desired result by creating QVD of the query and currently stuck in getting the report to be sync with the Calendar. For the same I have posted a reply to Krishna who is guiding me in fixing the issue. I look forward for your guidelines and approach to fix the current issue.

Regards..

[krishna20]

Dear Ankur ,

I didn't find any attachment with this thread.

Please answer for my doubts from your app.

1.Have you resolved the synthetic issue? If not resolve it ,by giving alias name to Month and Year.

2.If you got months in sequence  will you  reach your point??

3.Make sure the file has attached such that me or some one will reply you soon to resolve this issue.

Regards

Krishna