what about current month and prior month

[#1]

Hi ,

I would like to compare the data between current month and last month as below example, what about the expression about the statement?

Company Name	Year	Month	CurrentMonth(Qty)
A	2014	11	12
A	2014	12	15
A	2015	1	8
A	2015	2	6
B	2014	11	13
B	2015	1	22
B	2015	2	16
B	2015	3	14

I tried following statement, but it does not work.

CurrentMonth(Qty) : sum(stocks)

LastMonth(Qty): sum(${<Month={${=Max(Month)-1} sales )

Expecting result is like :

When select year2015, month 1, it would be

Company Name	Year	Month	CurrentMonth(Qty)	LastMonth(Qty)
A	2015	1	8	15
B	2015	1	22	13

Pls kindly help.

Thanks

Jessica

[Gysbert_Wassenaar]

sum(${<Month={${=Max(Month)-1} sales ) won't work because when you select month 1  then the previous month would be 1 - 1 = 0. Month 0 doesn't exist. You want month 12 of the previous year. So if you select a year and a month your expression needs to overrule those selections, because month 12 of the previous year doesn't exist in the year you selected.

If you have a date field in your data you probably can use that instead:

sum({$<Year=,Month=,Date={'>=$(=monthstart(max(Date),-1))<$(=monthstart(max(Date)))'}>}sales)

If you can't get it to work, post a qlikview document that demonstrates the problem.

[#3]

Hello Gysbert,

Attached pls find the orignial QV file, maybe we need to sumary total amount value of each month firstly, since there are multipl value in some of months. I tried your expression, it is still not working. Kindly pls help to check.

Thank you!

Regards,

Jessica

[#4]

Hi Gysbert,

I am too new here and don't fine the way to upload my QVD file. Do you know how to upload it?

Thanks&Regards

Jessica

[#5]

original data is:

Expected result:

Thanks&Regards,

Jessica