Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Qlik Analytics Tour 2020 Online. Begins August 10th. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Partner
Partner

Re: Set analysis to last month value.

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

Re: Set analysis to last month value.

Hi!

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

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

Best regards

Highlighted
Creator
Creator

Re: Set analysis to last month value.

Hi,
Its Not Working
Highlighted
Partner
Partner

Re: Set analysis to last month value.

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

Highlighted
Creator
Creator

Re: Set analysis to last month value.

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

Highlighted
Partner
Partner

Re: Set analysis to last month value.

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

Highlighted
Creator
Creator

Re: Set analysis to last month value.

thanks,its working