Announcements
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 - Specialist
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 - Creator II

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 - Specialist

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 - Specialist
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