summarize by month from non-date field - Qlik Community

fema3773 · ‎2017-02-09

HI,

I have the load script below. I believe I need it to extract the month from the posting date and summarize to the month level but I am not sure how to best go about this.
What I need is all data from 2016 which would be millions and millions of records at the day level. The result I need would be like:
Jan-16 Revenue
Feb-16 Revenue
Etc.

Load*;
SQL SELECT
       VU_DIM_DOCUMENT.SOURCE,
      VU_DIM_DOCUMENT.POSTING_DATE,
       VU_DIM_DOCUMENT.CREATED_BY_NAME,
       VU_FACT_FICA_K.COMPANY_CODE,
       VU_FACT_FICA_K.GL_ACCOUNT,
       VU_FACT_FICA_K.COST_CENTER,
       VU_FACT_FICA_K.AMOUNT,
       VU_FACT_FICA_K.SOURCE_SYSTEM,
       VU_FACT_FICA_K.PROFIT_CENTER
FROM Z22CUST.VU_DIM_DOCUMENT VU_DIM_DOCUMENT
       INNER JOIN Z22CUST.VU_FACT_FICA_K VU_FACT_FICA_K
          ON     (VU_DIM_DOCUMENT.DOCUMENT_ID = VU_FACT_FICA_K.DOCUMENT_ID)
             AND (VU_DIM_DOCUMENT.SOURCE_SYSTEM =
                     VU_FACT_FICA_K.SOURCE_SYSTEM)
WHERE        VU_FACT_FICA_K.SOURCE_SYSTEM = 'C2'
                  AND VU_DIM_DOCUMENT.POSTING_DATE >
                         TO_DATE ('2016-01-01 00:00:00',
                                  'yyyy/mm/dd hh24:mi:ss')

sunny_talwar · ‎2017-02-09

May be this:

LOAD *;

SQL SELECT

VU_DIM_DOCUMENT.SOURCE,

TRUNC(VU_DIM_DOCUMENT.POSTING_DATE, 'MONTH') as VU_DIM_DOCUMENT.POSTING_MONTH,

VU_DIM_DOCUMENT.CREATED_BY_NAME,

VU_FACT_FICA_K.COMPANY_CODE,

VU_FACT_FICA_K.GL_ACCOUNT,

VU_FACT_FICA_K.COST_CENTER,

Sum(VU_FACT_FICA_K.AMOUNT) as VU_FACT_FICA_K.AMOUNT,

VU_FACT_FICA_K.SOURCE_SYSTEM,

VU_FACT_FICA_K.PROFIT_CENTER

FROM

Z22CUST.VU_DIM_DOCUMENT VU_DIM_DOCUMENT

INNER JOIN

Z22CUST.VU_FACT_FICA_K VU_FACT_FICA_K

ON

(VU_DIM_DOCUMENT.DOCUMENT_ID = VU_FACT_FICA_K.DOCUMENT_ID)

AND (VU_DIM_DOCUMENT.SOURCE_SYSTEM = VU_FACT_FICA_K.SOURCE_SYSTEM)

WHERE

VU_FACT_FICA_K.SOURCE_SYSTEM = 'C2'

AND VU_DIM_DOCUMENT.POSTING_DATE > TO_DATE ('2016-01-01 00:00:00', 'yyyy/mm/dd hh24:mi:ss')

GROUP BY