13 Replies Latest reply: Jun 10, 2018 7:32 AM by Chanty 4u

# Previous Month Expression

Hi All,

can you please help me to populate previous month values, for the below sample set of data

data set

 Qty Month 1 01-01-2018 2 02-02-2018 3 03-03-2018 4 04-04-2018 5 05-05-2018 6 06-06-2018 7 07-07-2018 8 08-08-2018 9 09-09-2018 10 10-10-2018 11 11-11-2018 12 12-12-2018

Thanks,

Gowtham

• ###### Re: Previous Month Expression

Put -1 after max month in your expression. You can use previous function as well.

Month minus previous month | Qlik Community

• ###### Re: Previous Month Expression

i tried with but its not showing the result

Num(Sum({Month={"\$(=Max(Month)-1)"}>}Qty))

• ###### Re: Previous Month Expression

As Ishtdeep already said:

Num(Sum({<Month={"\$(=Max(Month)-1)"}>}Qty))

Let's do this:

Sum({<Month={">=\$(=MonthStart(AddMonths(Month,-1)))<=\$(=MonthEnd(AddMonths(Month,-1)))"}>}Qty)

• ###### Re: Previous Month Expression

This expression showing 0 values

• ###### Re: Previous Month Expression

I guess your month field is not in the date format. Make sure it's not a text field. Test this:

IsNum (Month)

If you get -1 as result, then your field is a numerical one. Otherwise, you must to format it into load script.

Cheers

• ###### Re: Previous Month Expression

yes its -1 how to change it now

• ###### Re: Previous Month Expression

try this

sum({<Month={"=max(Month)-1"}>}Qty)

• ###### Re: Previous Month Expression

Try this

F:

load *,Year(Date) as Year, Month(Date) as Month;

D:LOAD * INLINE [

Qty, Date

1, 01-01-2018

2, 02-02-2018

3, 03-03-2018

4, 04-04-2018

5, 05-05-2018

6, 06-06-2018

7, 07-07-2018

8, 08-08-2018

9, 09-09-2018

10, 10-10-2018

11, 11-11-2018

12, 12-12-2018

];

Previous Month Expression

=sum({<Year = {"=max(Year)"}, Month={"\$(=month(MonthStart(max(Date))-1))"}>}Qty)

• ###### Re: Previous Month Expression

Hi Shivesh,

Can you please provide me in expression without editing the script.

Thanks,

Gowtham

• ###### Re: Previous Month Expression

Sum({Month={"\$(=Max(Month),-1)"}>}Qty)

try this

• ###### Re: Previous Month Expression

I tried with same but still not result

Sum({Month={"\$(=Max(Month),-1)"}>}Qty)

• ###### Re: Previous Month Expression

sorry i made small mistake

use this:

Sum({Month={"\$(=Max(Month,-1))"}>}Qty)

• ###### Re: Previous Month Expression

Still not correct its showing -

• ###### Re: Previous Month Expression

try

a:

load *, month(MyDate) as NewMonth,

date(MyDate) as NewD,

Num(Month(MyDate)) as MonthNum;

LOAD Qty,

MyDate

FROM

[C:\Users\Lenovo\Desktop\test.xlsx]

(ooxml, embedded labels, table is Sheet3);

Frontend:

Strighttable

dim: Month

Exp: sum({<MonthNum={"=max(MonthNum)-1"}>}Qty)