Set Analysis with Variable ( Previous month)

[#1]

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!

[Anonymous]

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

[Michiel_QV_Fan]

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]

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.

[#5]

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

[#6]

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 · Accepted solution]

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.

[senpradip007]

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

[linusblomberg]

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

[sasiparupudi1]

vPrevMonth should be as follows

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