Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

frantzgnz
New Contributor II

Get how many tickets were generated in the month before the chosen one

Hello Community! I'm just entering what is Qlik Sense and I have a doubt that I can not solve it for several days.

I want to get how many tickets have been made in the month before the one chosen. For example, I choose the month March of the Year 2018, and I want to show the Number of Tickets for March and also, for February.

The code to appear the previous month, I can not find it. I attach the data.

The important column is: Fecha_Crea

Thanks in advance

1 Solution

Accepted Solutions
Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

You should term Month on Month Comparison


Anyway, use below expression,

Count({  < Num_Fecha = {  $(=Max(Num_Fecha) - 1)},[Fecha_Crea.autoCalendar.Month] >}Ticket)

2018-07-10_140847.png

I selected Jun and 1st KPI shows June Count and 2nd KPI shows May Count.

15 Replies
Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Count({  <Mes= {"<=$(=num(Mes))"} >}Ticket)

This will give you # of tickets for selected Month and Previous Month for Selected Year.

Ex: If you Select Year 2018, Month March, this will give tickets for Jan-2018,Feb-2018 and Mar-2018.

Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Sorry I misread the requirement...

In your script create a new field

Num(Month(Fecha_Crea)) as Num_Mes

In Front end use...

Count({  <Num_Mes= {  $(=Max(Num_Mes)  )}>+<Mes= {"<=$(=num(Mes)-1)"},

Num_Mes= {  $(=Max(Num_Mes) - 1)} >}Ticket)

frantzgnz
New Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Can u please attach the .qvf?

Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Tickets:

LOAD Count,

     Ticket,

     Fuente,

     Fecha_creacion,

     Mes,

     Año,

     Fecha_Crea ,

     Month(Fecha_Crea) as FechaMonth,

     Num(Month(Fecha_Crea)) as Num_Fecha,

     Hora_1,

     Fecha_cierre,

     Fecha_Cie,

     Hora_2,

     Hora_Salida,

     Hora_ref1,

     Hora_Ingreso,

     Hora_Ref2,

     Fecha_Ini,

     Fecha_Fin,

     [Dias Lab],

     Hora_Lab,

     Total_HORAS,

     Estado,

     Dpto,

     SLA,

     SLA2,

     Cumplimiento,

     F26

From........ YourXL file destination;

and in front end use

Count({  <Num_Fecha = {  $(=Max(Num_Fecha)  )}>+<FechaMonth= {"<=$(=num(FechaMonth)-1)"},

Num_Fecha = {  $(=Max(Num_Fecha) - 1)} >}Ticket)

Sorry attaching the qvf is like importing/exporting the files in QMC...

This will help too, just copy paste the script once you have loaded the table

agigliotti
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

I suggest you to use the below script for rolling 2 month calculations:


AsOfMonth:
load 'Current' as Type,
Month as Month_AsOf,
Month as Month
From........ YourXL file destination;

Concatenate (AsOfMonth)
load 'Rolling 2' as Type,
Month as Month_AsOf,
Month + 1 - IterNo() as Month
From........ YourXL file destination
while IterNo() <= 2;

right join load Month From........ YourXL file destination;

Finally in UI you can use the below expressions:

for current month: count( {< Type = {'Current'} >} Ticket )

for rolling 2 month: count( {< Type = {'Rolling 2'} >} Ticket )


I hope it helps.

frantzgnz
New Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Hi, I tried but it show me Current Month Value. I want the previous Month Value. Just the Previous Month. (If I select June, it should show the values ​​of June and MayProblem.png

Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

can you attach your QVF file here?

frantzgnz
New Contributor II

Re: Get how many tickets were generated in the month before the chosen one

Here it is.

Khan_Mohammed
Honored Contributor II

Re: Get how many tickets were generated in the month before the chosen one

The XL file you gave initially has missing fields.

Please provide complete set of data

Community Browser