Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
HeatherC
Contributor II
Contributor II

Prior Month value using a variable

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!

 

Labels (2)
7 Replies
NitinK7
Specialist
Specialist

can you provide  sample data file

HeatherC
Contributor II
Contributor II
Author

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

Thanks for any help.

NitinK7
Specialist
Specialist

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
Contributor II
Contributor II
Author

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

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
Contributor II
Contributor II
Author

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

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.