topic Re: Prior Month value using a variable in QlikView

Prior Month value using a variable

HeatherC — Sat, 16 Nov 2024 18:51:41 GMT

I've read numerous postings on this topic, but just can't get it to work. I have a table where I need to post the sum(Denominator) for the current and prior month.

I have a variable defined in my load script

vPriorMonthDate=monthend(addmonths(max(Date),-1))

I checked the value of vPriorMonthDate using an InputBox and it evaluate properly to 1/31/2020 when the Date selected is 2/29/2020.

In the table expression, I'm using the following:

=Sum({$<Date={"$(=$(vPriorMonthDate))"}>}Denominator) and it evaluates to 0.

I can't figure out what I'm missing.

Thanks!

Re: Prior Month value using a variable

NitinK7 — Tue, 31 Mar 2020 13:58:18 GMT

can you provide sample data file

Re: Prior Month value using a variable

HeatherC — Tue, 31 Mar 2020 14:19:03 GMT

Here is a sample data file with just 2 months of data.

Thanks for any help.

Re: Prior Month value using a variable

NitinK7 — Tue, 31 Mar 2020 14:38:05 GMT

Hi,

your expression is looking good

don't create variable in load script

need to create manually (got to setting select variable overview and create variable)

then it is working ..

Thanks,

Nitin

Re: Prior Month value using a variable

HeatherC — Tue, 31 Mar 2020 15:11:35 GMT

I've tried creating the variable in the variable overview (although our qlikview administrator said to only create them in the load script) and the expression did not work.

Why would it matter where the variable was created?

Re: Prior Month value using a variable

NitinK7 — Tue, 31 Mar 2020 15:27:36 GMT

try below

BACKEND

LOAD Team,
MEASURE,
Division,
PROVIDER,
NUM,
DEN,
Date,
Target,
Population
FROM
SampleData.xlsx
(ooxml, embedded labels, table is SampleData);

Set vPriorMonthDate=monthend(addmonths(max(Date),-1)); //set variable

Expression

=Sum({$<Date={'$(=$(vPriorMonthDate))'}>}Denominator)

it is working for me.

thanks,

Nitin.

Re: Prior Month value using a variable

HeatherC — Tue, 31 Mar 2020 16:21:04 GMT

Thanks for your help.

I started a fresh qvw, used my real data file and your formulas and it works just fine. I try it in my existing qvw and it doesn't work.

If I add =$(vPriorMonthDate) to my table, it evaluates the the last day of the previous month.

If I add =Sum({$<Date={'$(=$(vPriorMonthDate))'}>}Denominator) it evaluates to 0

Could something in our calendar be messing this up (but why would =$(vPriorMonthDate) evaluate properly?

Re: Prior Month value using a variable

Brett_Bleess — Wed, 22 Apr 2020 20:56:42 GMT

Have a look at the following Design Blog post, might give you some ideas as to what is going wrong:

https://community.qlik.com/t5/Qlik-Design-Blog/Dates-in-Set-Analysis/ba-p/1472511

If you want to search further in the Design Blog area for other posts, use the following link:

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Regards,
Brett