YEAR / months - Page 2 - Qlik Community

fmazzarelli · ‎2015-07-01

Hi,

i would create a variable dividing the year in 2 - 3 - 4 - 5 months.

a field "data" is colled DATE_ORD - YYYY/MM/GG

How could I do?

2 months:

gen - feb

mar - apr

mag - giu

lug - ago

set - ott

nov - dic

3 months:

gen - feb - mar

apr - mag - giu

lug - ago -set

ott - nov - dic

4 month

gen - feb - mar - apr

mag - giu lug - ago

set - ott - nov - dic

6 months
gen - feb - mar - apr - mag - giu

lug - ago - set - ott - nov - dic

fmazzarelli · ‎2015-07-01

i afraid but probably my request was bad presented.

i need to Group the months

Example

gen + feb as a criteria of selection of all items included in gen + feb and so on

swuehl · ‎2015-07-01

That's exactely what I intended to do.

Please check attached sample.

pratap6699 · ‎2015-07-01

Ceil( Month(DATE_ORD) / 2 ) As date,

based on requirement we can change the condition

fmazzarelli · ‎2015-07-01

hi

upload an image

swuehl · ‎2015-07-01

You are not using Ceil() function in your Bimestre field generation, right?

If you want to map the number 1,2,3,4,5,6 to Jan-Feb, Mar-Apr,... you can do it like this:

TwoMonthMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

LOAD *, ApplyMap('TwoMonthMap', TwoMonthPeriod) as TwoMonthPeriodName;

LOAD *,

Ceil( Month(DATE_ORD) / 2 ) As TwoMonthPeriod,

Ceil( Month(DATE_ORD) / 3 ) As ThreeMonthPeriod,

Ceil( Month(DATE_ORD) / 4 ) As FourMonthPeriod,

Ceil( Month(DATE_ORD) / 6 ) As SixMonthPeriod;

LOAD *, Month(DATE_ORD) as Month;

LOAD Date(makedate(2014)+recno()-1,'YYYY-MM-DD') AS DATE_ORD

AUTOGENERATE 365;

fmazzarelli · ‎2015-07-01

i'm watching a video about qlik.... but I'm using ceil function.

Meanwhile, I attach my script.

my script is

LOAD *,

Ceil(Month(FATT_DAT))/2 AS BIMESTRE,

Ceil(Month(FATT_DAT))/3 AS TRIMESTRE,

Ceil(Month(FATT_DAT))/4 AS QUADRIMESTRE,

Ceil(Month(FATT_DAT))/6 AS SEMESTRE;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

,[FATT_TIPKY]

,[FATT_CLIENTEKY]

,[FATT_PAR_IVA]

,[FATT_COD_FIS]

,[FATT_VALUTAKY]

,[FATT_CAMBIO]

,[FATT_COD_PAGKY]

,[FATT_COD_BANKY]

,[FATT_NOTE]

,[FATT_NOTE_STAMPA]

,[FATT_NUMERO]

,[FATT_APPKY]

,[FATT_ID_APPKY]

,[FATT_IND_ANA_INDKY]

,[FATT_STAMPA]

,[FATT_TRAVASO]

,[FATT_DICH_INT]

,[FATT_PROVA]

,[FATT_TAR_CLIKY]

,[FATT_TOT_IVA]

,[FATT_TOT]

,[FATT_TOT_IMPONIBILE]

,[FATT_SERIE]

,[FATT_FATT_ID_SOCIOKY]

,[FATT_NOTE_STAMPA_TIPO]

,[FATT_NC_FATT_STORKY]

,[FATT_SOC_CLIENTEKY]

,[FATT_SOC_COD_PAGKY]

,[FATT_SOC_COD_BANKY]

,[FATT_SOC_SOC]

,[FATT_SOC]

,[FATT_NUMERO_EXT]

,[FATT_COMP_ANNO]

,[FATT_COMP_MESE]

,[FATT_TIP_GRP]

,[FATT_TIP_GRP_IDENT]

,[FATT_BOLLO_VIRTUALE]

,[FATT_GRFKY]

,[FATT_IBAN]

,[FATT_BIC]

, year(FATT_DAT) as Anno

, month(FATT_DAT) as Mese

FROM [FreightEurocoop].[dbo].[FATTURE_TESTATA]

inner join ANABASE

ON ANAB_KY = FATT_CLIENTEKY;

swuehl · ‎2015-07-01

You need to enclose the division by 2 into the ceil() function call.

BimestreMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

Table:

LOAD *, Applymap('BimestreMap', BIMESTRE) as BIMESTRE_TEXT;

LOAD *,

Ceil(Month(FATT_DAT) /2) AS BIMESTRE,

Ceil(Month(FATT_DAT) /3) AS TRIMESTRE,

Ceil(Month(FATT_DAT) /4) AS QUADRIMESTRE,

Ceil(Month(FATT_DAT) /6) AS SEMESTRE;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

,[FATT_TIPKY]

,[FATT_CLIENTEKY]

,[FATT_PAR_IVA]

,[FATT_COD_FIS]

,[FATT_VALUTAKY]

,[FATT_CAMBIO]

,[FATT_COD_PAGKY]

,[FATT_COD_BANKY]

,[FATT_NOTE]

,[FATT_NOTE_STAMPA]

,[FATT_NUMERO]

,[FATT_APPKY]

,[FATT_ID_APPKY]

,[FATT_IND_ANA_INDKY]

,[FATT_STAMPA]

,[FATT_TRAVASO]

,[FATT_DICH_INT]

,[FATT_PROVA]

,[FATT_TAR_CLIKY]

,[FATT_TOT_IVA]

,[FATT_TOT]

,[FATT_TOT_IMPONIBILE]

,[FATT_SERIE]

,[FATT_FATT_ID_SOCIOKY]

,[FATT_NOTE_STAMPA_TIPO]

,[FATT_NC_FATT_STORKY]

,[FATT_SOC_CLIENTEKY]

,[FATT_SOC_COD_PAGKY]

,[FATT_SOC_COD_BANKY]

,[FATT_SOC_SOC]

,[FATT_SOC]

,[FATT_NUMERO_EXT]

,[FATT_COMP_ANNO]

,[FATT_COMP_MESE]

,[FATT_TIP_GRP]

,[FATT_TIP_GRP_IDENT]

,[FATT_BOLLO_VIRTUALE]

,[FATT_GRFKY]

,[FATT_IBAN]

,[FATT_BIC]

, year(FATT_DAT) as Anno

, month(FATT_DAT) as Mese

FROM [FreightEurocoop].[dbo].[FATTURE_TESTATA]

inner join ANABASE

ON ANAB_KY = FATT_CLIENTEKY;

fmazzarelli · ‎2015-07-01

sorry, I corrected my script as below

and it seems work fine.

Only a doubt as when I select the period Jan-Feb, the month FEB is selected.

I have to check the values

Script is:

TwoMonthMap:

MAPPING LOAD *;

LOAD F1, dual(F2,F1) as F3 INLINE [

F1,F2

1, Jan-Feb

2, Mar-Apr

3, May-Jun

4, Jul-Aug

5, Sep-Oct

6, Nov-Dec

];

LOAD *, ApplyMap('TwoMonthMap', BIMESTRE) as TwoMonthPeriodName;

LOAD *,

Ceil(Month(FATT_DAT))/2 AS BIMESTRE;

// Ceil(Month(FATT_DAT))/3 AS TRIMESTRE,

// Ceil(Month(FATT_DAT))/4 AS QUADRIMESTRE,

// Ceil(Month(FATT_DAT))/6 AS SEMESTRE;

LOAD *, Month(FATT_DAT) as Month;

LOAD Date(makedate(2015)+recno()-1,'YYYY-MM-DD') AS FATT_DAT

AUTOGENERATE 365;

SQL Select [ANAB_RAG_SOC]

,[FATT_KY]

,[FATT_DAT]

MarcoWedel · ‎2015-07-01

Hi,

one solution could be also:

SET MonthNames='Gen;Feb;Mar;Apr;Mag;Giu;Lug;Ago;Set;Ott;Nov;Dic';

tabMesi:
LOAD *,
    Month(FATT_DAT) as Mese,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),2,1)*4-3, 7),';',' - '),Floor(Month(FATT_DAT),2,1)) as BIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),3,1)*4-3,11),';',' - '),Floor(Month(FATT_DAT),3,1)) as TRIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),4,1)*4-3,15),';',' - '),Floor(Month(FATT_DAT),4,1)) as QUADRIMESTRE,
    Dual(Replace(Mid('$(MonthNames)',Floor(Month(FATT_DAT),6,1)*4-3,23),';',' - '),Floor(Month(FATT_DAT),6,1)) as SEMESTRE;
// Test data generation
LOAD Date(MakeDate(2015)+RecNo()-1,'YYYY/MM/DD') as FATT_DAT
AutoGenerate 365;

hope this helps

regards

Marco

fmazzarelli · ‎2015-07-02

now, i'm having a meeting.

I'll test your solution as soon as possible