Qlik Community

Ask a Question

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

View solution in original post

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

priyalvp24
Creator
Creator
Author

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

Please try

Aiolos Zhao

priyalvp24
Creator
Creator
Author

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

uacg0009
Partner
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

View solution in original post

priyalvp24
Creator
Creator
Author

thanks,its working