Previous Month Function

[#1]

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

[#2]

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

[Anonymous]

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.

[montero91]

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

[chiru_thota]

Have a look at attached QVW and screen

[rbecher]

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

[#7]

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 .