topic Re: Previous Month Function in QlikView

Previous Month Function

Mon, 03 Mar 2014 17:23:06 GMT

Hi,

I have a selection date which contain YEAR, MONTHNAME, QUARTER and DAY of my INVOICE_DATE.

I want to show, in a table, the value for max(Monthname()-1). The textbox should always show the Value of Sum(sales) for the Current Month and the previous one. (MONTHNAME-1)

The formula is:

Sum(AMTATI_0*SNS_0 ) that show me the sum of the MOnth selected for the same year, and when I select Januarry 2013, I want to display that month and Decembre 2012.

How can I display the sum of the previous MONTH??

How should I construct such a formula ?

Thanks

Re: Previous Month Function

Mon, 03 Mar 2014 17:37:47 GMT

Hi,

I think you will need to add 2 fields:

1) YearMonth field, where 1 is the first month of the first year of the db, and you increment by one every month

So that -1 will go back to previous month, whatever the month and year, especially January

2) Sales: in the load compute AMTATI_0*SNS_0 as Sales so that you just do Sum(Sales)

May I recommend you to read the doc on set analysis: http://community.qlik.com/docs/DOC-4951

I think your solution is in chapter 4.6.1

Fabrice

Re: Previous Month Function

Anonymous — Mon, 03 Mar 2014 17:47:50 GMT

In the script create this field

LOAD *,

dual([YEAR] & '-' & [MONTHNAME],Date(MonthStart([Date]))) as [Year-Month];

LOAD

Date,
Year(Date) as YEAR,

Month(Date) as MONTHNAME,

etc,

FROM .....;

Now use Year-Month in your calculation. When you subtract by 1 it will revert to the prior year at January.

Re: Previous Month Function

montero91 — Mon, 03 Mar 2014 18:04:44 GMT

HI JB

Del calendario imagino que tienes un campo Fecha DD-MM-YYYY.

Si este es el caso puedes utilizar la función AddMonths. El cual a la fecha le puedes agregar o quitar meses.

Para este caso, si tu campo mes se llama Mes y año se llama Año el ejemplo es:

Formula mes actual

Sum ( {<Yeay={$(=Year(max(DATE)))},Month={$(=Month(max(DATE))}>} AMTATI_0 * SNS_0)

Formula mes anterior

Sum ( {<Yeay={$(=Year(AddMonths (max(DATE),-1)))},Month={$(=Month(AddMonths (max(DATE),-1))}>} AMTATI_0
* SNS_0)

Esto es de manera dinámica si no tienes selección de periodo lo que te da como resultado es sobre el mes y año máximo, si seleccionan un mes o año anterior en ese momento ese mes o año se convierten en el mes y año máximo

-------------------------------------------------------

in your calendar guess you have a DD-MM-YYYY Date field.

If this is the case you can use the function AddMonths. this funtion you can add or remove months.

In this case, for example if you field is called Month and Year

the example is:

Current Month Formula

Sum ( {<Yeay={$(=Year(max(DATE)))},Month={$(=Month(max(DATE))}>} AMTATI_0 * SNS_0)

Formula previous month

Sum ( {<Yeay={$(=Year(AddMonths (max(DATE),-1)))},Month={$(=Month(AddMonths (max(DATE),-1))}>} AMTATI_0
* SNS_0)

This is dynamically if you have selected period giving you result is maximum on the month and year, if they select a month or last year at this time that month or year become the highest month and year

Re: Previous Month Function

chiru_thota — Mon, 03 Mar 2014 18:18:43 GMT

Have a look at attached QVW and screen

Re: Previous Month Function

rbecher — Mon, 03 Mar 2014 18:45:24 GMT

Hi,

this can be done with Set Analysis and AddMonths(). If you would upload a sample with the fields you have one can build a solution.

- Ralf

Re: Re: Previous Month Function

Tue, 04 Mar 2014 10:29:40 GMT

Hi Ralf,

I Can't Upload the project , but I can describe what I have in exprssions and Date Fields loaded:

UNQUALIFY *;

MASTER_DATE:

LOAD SINVOICEV.INVDAT_0,

Date(SINVOICEV.INVDAT_0) as SINVOICEV.INVDAT_Date,

Day(SINVOICEV.INVDAT_0) as DAYNUMBER,

Week (SINVOICEV.INVDAT_0) AS WEEK,

Month(SINVOICEV.INVDAT_0) AS MONTHNAME,

Num (Month(SINVOICEV.INVDAT_0)) AS MONTHNUMBER,

num(RowNo()) as MONTHNO,

'Trimestre' & CEIL (Num (month(SINVOICEV.INVDAT_0))/3) as QUARTER,

'Semestre'& CEIL (Num (month(SINVOICEV.INVDAT_0))/6) as SEMESTRE,

YEAR (SINVOICEV.INVDAT_0) As YEAR

Resident SINVOICEV;

UNQUALIFY *;

CalendarTemp:

LOAD

Max(SINVOICEV.INVDAT_0) AS DateMax,

Min(SINVOICEV.INVDAT_0) AS DateMin

Resident MASTER_DATE;

This is the Date Table , for the function , Itried that one and it doesnt works:

=sum( {<Year={$(=Year(AddMonths (max(SINVOICEV.INVDAT_Date),-1)))},MONTHNAME={$(=MONTH(AddMonths (max(SINVOICEV.INVDAT_Date),-1)))}>} Sales)

I want to display Sales for the month select and the previous one .