Prior Month value using a variable

[HeatherC]

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!

[NitinK7]

can you provide  sample data file

[HeatherC]

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

Thanks for any help.

[NitinK7]

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

[HeatherC]

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?

[NitinK7]

hi

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.

[HeatherC]

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?

[Brett_Bleess]

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