Skip to main content
Announcements
See why Qlik was named a Leader in the 2024 Gartner® Magic Quadrant™ for Data Integration Tools for the ninth year in a row: Get the report
cancel
Showing results for 
Search instead for 
Did you mean: 
priyalvp24
Creator
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:
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

 

 

 

Labels (1)
1 Solution

Accepted Solutions
uacg0009
Partner - Specialist
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

View solution in original post

6 Replies
Zhandos_Shotan
Partner - Creator II
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

priyalvp24
Creator
Creator
Author

Hi,
Its Not Working
uacg0009
Partner - Specialist
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

Please try

Aiolos Zhao

priyalvp24
Creator
Creator
Author

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

uacg0009
Partner - Specialist
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
priyalvp24
Creator
Creator
Author

thanks,its working