# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for
Did you mean:
Creator

## Set analysis to last month value.

Hi All,

I am facing the the issue in calculating the last month value.

I am using the fiscal calendar starting from April to March.

Below is my master calendar code.

temp_date:
min(DocDate) as start_date,
Max(DocDate) as end_date
Resident Revenue;

Let vstart_date=peek('start_date',0,temp_date);
Let vEnd_date=peek('end_date',0,temp_date);

drop Table temp_date;

TempCalendar:
date(\$(vstart_date) + rowno() - 1) As DocDate
Autogenerate
\$(vEnd_date) - \$(vstart_date) + 1;

MasterCalendar:
DocDate,
num(Month(DocDate)) as DocMonth,
Month(DocDate) AS Doc_Month,
If(Month(DocDate) > 0 and Month(DocDate) < 4, 'FY-'&(Year(DocDate)-1)&'-'&Right(Year(DocDate),2),'FY-'&Year(DocDate)&'-'&Right((Year(DocDate)+1),2)) as DocYear_Filter,
If(Month(DocDate) > 0 and Month(DocDate) < 4, Year(DocDate)) as DocYear

resident TempCalendar;

DROP Table TempCalendar;

I want to calculate the last month value.

I have used the following expression.

sum({<DocMonth={"\$(=max({<DocYear={'\$(=max(DocYear))'}>}DocMonth)-1)"}>}     [LY in Rs])

It should calculate the value for December 2018 as the current month is January 2019.

But its giving me the value as 0.

Regards,

Priyal

Labels (1)
• ### New to Qlik Sense

1 Solution

Accepted Solutions
Partner
Hi,
like I said, maybe you need to add a new column:
monthname(DocDate) as Doc_Monthname
then use below expression:
sum({<Doc_Monthname={"\$(=monthname(max(Doc_Monthname),-1))"}>} [LY in Rs])
Please try, it worked in my desktop.
Aiolos Zhao
6 Replies
Partner

Hi!

Try to replace single quotes from {'\$(=max(DocYear))'} to brackets [...]:

sum({<DocMonth={"\$(=max({<DocYear={[\$(=max(DocYear))]}>}DocMonth)-1)"}>}     [LY in Rs])

Best regards

Creator
Author
Hi,
Its Not Working
Partner

Hi,

I think the problem is :

max(Docyear) is 2019

in 2019 max(month) = 1

and in 2019 max(month)  - 1 = 0

so I get the zero value

I think it's better to create another field, like monthname or something else including year and month

Aiolos Zhao

Creator
Author

Hi Aiolos Zhao,
It will be a great help if you sent the expression.

Partner
Hi,
like I said, maybe you need to add a new column:
monthname(DocDate) as Doc_Monthname
then use below expression:
sum({<Doc_Monthname={"\$(=monthname(max(Doc_Monthname),-1))"}>} [LY in Rs])
Please try, it worked in my desktop.
Aiolos Zhao
Creator
Author
thanks,its working
Community Browser