Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Set Analysis with Variable ( Previous month)

Hi, guys!

I need to create an expression in straight table summing values of previous month . I've created a variable for previous month: vPrevMonth=Date((Month(Today())-1),'MMM-YY')

I try an expression like :Sum({<Vacation_Date={$(vPrevMonth)}>} Days), but it doesnt work.

Vacation_Date format is 'MMM-YY'.

May be I have a mistake in my syntax?

Sample application is attached.

Thank You!

1 Solution

Accepted Solutions
linusblomberg
Creator II
Creator II

There are several things that can be said about your data model. You should look it over.

Anyway, quick fix:

Variable (note that I removed the "=")

Date((Monthname(Today())-1),'MMM-YY')

Expression

Sum({<Vacation_Date={"$(=$(vPrevMonth))"}>} Days)

Tip: Create you variables in the script. i.e let vPrevMonth = 'Date((Monthname(Today())-1),'MMM-YY')';

having a "=" in the variable editor will reduce performance as they are constantly re-evaluated. evaluate your variables when you need them.

I would avoid dates in the form of MMM-YY. use numeric or integer keys.

View solution in original post

12 Replies
Anonymous
Not applicable
Author

Sum({<Vacation_Date={'$(vPrevMonth)'}>} Days)

michielvandegoo
Specialist
Specialist

Create a additional field in your calendar like:

autonumber(Month_Date) as Month_ID. this will give a list from 1 to ??

then in your set analysis:

sum({<Month_ID = {="$(=max(Month_ID)-1)"}>}Days) where the max month_id is the latest month in the data or the latest month selected.

MK_QSL
MVP
MVP

Few issues I found

1) Your Month_name is having MMM format and vCurMonth is having MMM-YY so your Calculation Dimension is Failing

2) vPreMonth need to be defined as Date((MonthStart(Today())-1),'MMM-YY')

3) Remove = from all your Variables Definitions and use $ sing expansion while calling or using them in charts/tables dimension or expression.

Not applicable
Author

Unfortunately, it's not working, I got zeros as a result

Not applicable
Author

The thing is I want to sum values of previous month, meaning before actual current month. I dont need the previous month of selected months

linusblomberg
Creator II
Creator II

There are several things that can be said about your data model. You should look it over.

Anyway, quick fix:

Variable (note that I removed the "=")

Date((Monthname(Today())-1),'MMM-YY')

Expression

Sum({<Vacation_Date={"$(=$(vPrevMonth))"}>} Days)

Tip: Create you variables in the script. i.e let vPrevMonth = 'Date((Monthname(Today())-1),'MMM-YY')';

having a "=" in the variable editor will reduce performance as they are constantly re-evaluated. evaluate your variables when you need them.

I would avoid dates in the form of MMM-YY. use numeric or integer keys.

View solution in original post

senpradip007
Specialist III
Specialist III

Have you check the date format of the variable with respect to Vacation_Date format?

linusblomberg
Creator II
Creator II

Yes the variable doesn't work as the variable produce "Jan-00".

sasiparupudi1
Master III
Master III

vPrevMonth should be as follows

=Date((Today()-1),'MMM-YY')