topic Re: Set analysis to last month value. in QlikView

Set analysis to last month value.

priyalvp24 — Sat, 16 Nov 2024 04:51:44 GMT

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

Re: Set analysis to last month value.

Zhandos_Shotan — Thu, 03 Jan 2019 08:29:07 GMT

Hi!

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

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

Best regards

Re: Set analysis to last month value.

priyalvp24 — Thu, 03 Jan 2019 08:42:23 GMT

Hi,
Its Not Working

Re: Set analysis to last month value.

uacg0009 — Thu, 03 Jan 2019 08:56:49 GMT

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

Re: Set analysis to last month value.

priyalvp24 — Thu, 03 Jan 2019 11:08:42 GMT

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

Re: Set analysis to last month value.

uacg0009 — Fri, 04 Jan 2019 02:01:18 GMT

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

Re: Set analysis to last month value.

priyalvp24 — Tue, 08 Jan 2019 05:23:34 GMT

thanks,its working