Set analysis to last month value.

[priyalvp24]

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:
Load
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:
Load
date($(vstart_date) + rowno() - 1) As DocDate
Autogenerate
$(vEnd_date) - $(vstart_date) + 1;

MasterCalendar:
Load
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.

Please Help.

Regards,

Priyal

[Zhandos_Shotan]

Hi!

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

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

Best regards

[priyalvp24]

Hi,
Its Not Working

[uacg0009]

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

Please try

Aiolos Zhao

[priyalvp24]

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

[uacg0009 · Accepted solution]

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

[priyalvp24]

thanks,its working