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: 
MarioCenteno
Creator III
Creator III

Current Month Budget Expression

Hi

I am trying to show the budget of the current month but it is not showing correctly, the actual data of the month if it is showing correctly, could you help me to identify my problem?

Green box = correct

Red box = Wrong

data.png

3 Replies
MarioCenteno
Creator III
Creator III
Author

Load Script

let vMaxDay  = '=day(max(F_IFR))';

let vMaxDate  = '=max(F_IFR)'

let vMaxMonth  = '=month(max(F_IFR))';

let vMaxYear  = '=max(Year)';

let vPriorMonth  = '=month(addmonths(max(F_IFR),-1))';

let vPriorMonthYear = '=Year(addmonths(max(F_IFR),-1))';

let vPriorYear  = '=vMaxYear-1';

let vPriorYearDate = '=date(addyears(max(F_IFR),-1),' & chr(39) & 'DD MMM YYYY' & chr(39) & ')';

[QLK_BUDGET]:

SELECT to_char(PERIODO,'YYYYMM') as PERIODO,

"COD_UNICOM",

"META_MES" AS BUDGET_MONTH,

"META_ACUM" AS BUDGET_YTD

FROM QLK_BUDGET_RECUP

where to_char(PERIODO,'YYYYMM')>='20150101' and to_char(PERIODO,'YYYYMM')<='20180801';

[TR150]:

Load

COD_UNICOM,

TOTAL_KWH AS KWH,

"TOTAL_($)" AS MONTO,

FPTACOBRO AS F_IFR,

Year([FPTACOBRO]) as Year,

  Month(FPTACOBRO) as Month,

  Day(FPTACOBRO) as Day,

  Date(monthstart(FPTACOBRO), 'MM-YYYY') as MonthYear;

SELECT                             R.COD_UNICOM                ,

       decode(r.TIP_FACT, 'FT024','Refactura', 'FT032', 'Refactura', 'Factura') as tipo,

   R.COD_TAR AS "TARIFA",

   R.TIP_REC,

   R.TIP_CLI AS "TIPCLIENTE",

       r.nic,

   R.NIS_RAD AS "NISRAD",

   TO_CHAR(R.F_FACT, 'DD/MM/YYYY') AS FFACTURA,

       R.F_PUESTA_COBRO AS FPTACOBRO,

      SUM(DECODE(I.CO_CONCEPTO, 'CC221', I.CSMO_FACT,

                'CC250',I.CSMO_FACT, 'CC270', I.CSMO_FACT,

                'CC280', I.CSMO_FACT, 0.0)) AS TOTAL_KWH,                  

       (SUM(I.IMP_CONCEPTO)) AS "TOTAL_($)",

       max((Select max(desc_cod)

            From TRABPEND_FR@OPENPROD F, codigos@OPENPROD c

            Where r.nis_rad = f.nis_rad

            and r.sec_nis = f.sec_nis

            and r.imp_tot_rec = f.imp_fra

            and c.cod = f.cod_clase)) as "clasificacion",

        max((Select max(desc_tipo)

            From fraudes_concepto@OPENPROD a, tipos@OPENPROD t, TRABPEND_FR@OPENPROD F

            Where r.nis_rad = f.nis_rad

            and r.sec_nis = f.sec_nis

            and r.imp_tot_rec = f.imp_fra

            and a.NUM_FR = F.NUM_FR

            and t.tipo = a.tip_fra)) as tipo_fra ,

        max((Select max(f.num_fr)

            From fraudes_concepto@OPENPROD a, tipos@OPENPROD t, TRABPEND_FR@OPENPROD F

            Where r.nis_rad = f.nis_rad

            and r.sec_nis = f.sec_nis

            and r.imp_tot_rec = f.imp_fra

            and a.NUM_FR = F.NUM_FR

            and t.tipo = a.tip_fra)) as num_fr    

FROM IMP_CONCEPTO@OPENPROD I,

RECIBOS@OPENPROD R,

CODIGOS@OPENPROD G

WHERE /*R.COD_UNICOM BETWEEN  2000 AND 2999

  AND */R.f_puesta_cobro >=  To_date('20180701','YYYYMMDD') //and R.f_puesta_cobro <= To_date('20180731','YYYYMMDD')

  AND I.CO_CONCEPTO = G.COD

--  AND I.CO_CONCEPTO IN ('CC100','CC101','CC105','CC220','CC221','CC250','CC270','CC280','CC420','CC320','CC290','CC291','CC106','VA100', 'VA109')

--  AND R.TIP_FACT NOT IN ('FT024','FT032')

--  AND R.SIE_ERROR = 'AI777'

  --AND R.TIP_REC IN ('TR010','TR014','TR050')

  AND R.TIP_REC ='TR050'

  AND r.est_act <> 'ER600'

  AND r.est_act <> 'ER610'

  AND r.est_act <> 'ER625'

  AND R.F_FACT = I.F_FACT

  AND R.SEC_REC = I.SEC_REC

  AND R.SEC_NIS = I.SEC_NIS

  AND R.NIS_RAD = I.NIS_RAD

GROUP BY

   R.COD_UNICOM,

       r.tip_fact,

   R.COD_TAR,

   R.TIP_REC,

   R.TIP_CLI,

       r.nic,

   R.NIS_RAD,

   R.SEC_NIS,

   R.SEC_REC,

   R.F_FACT,

   R.F_PUESTA_COBRO;

balabhaskarqlik

What are the expressions, you're using in the object?

MarioCenteno
Creator III
Creator III
Author

I have already solved it, now I have problems with showing the YTD from last year. The problem is that when I select a month for example July 2018 I would have to show the accumulated from January to July 2017 but only shows data for July 2017 does not add, I want to have the cumulative.

YTD prior Year:

SUM({$<[F_IFR.autoCalendar.Date]=

{'>=$(=AddMonths(YearStart(Today()),-12))<=$(=AddMonths(MonthEnd (Today()),-12))'}>}KWH/1000)

qlik.png