topic Re: Set Analysis with Variable ( Previous month) in QlikView

Set Analysis with Variable ( Previous month)

Wed, 08 Jul 2015 08:30:32 GMT

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!

Re: Set Analysis with Variable ( Previous month)

Anonymous — Wed, 08 Jul 2015 08:35:10 GMT

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

Re: Set Analysis with Variable ( Previous month)

Michiel_QV_Fan — Wed, 08 Jul 2015 08:37:55 GMT

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.

Re: Set Analysis with Variable ( Previous month)

MK_QSL — Wed, 08 Jul 2015 08:41:32 GMT

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.

Re: Set Analysis with Variable ( Previous month)

Wed, 08 Jul 2015 08:42:14 GMT

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

Re: Set Analysis with Variable ( Previous month)

Wed, 08 Jul 2015 08:45:44 GMT

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

Re: Set Analysis with Variable ( Previous month)

linusblomberg — Wed, 08 Jul 2015 08:45:55 GMT

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.

Re: Set Analysis with Variable ( Previous month)

senpradip007 — Wed, 08 Jul 2015 08:53:00 GMT

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

Re: Set Analysis with Variable ( Previous month)

linusblomberg — Wed, 08 Jul 2015 08:57:28 GMT

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

Re: Set Analysis with Variable ( Previous month)

sasiparupudi1 — Wed, 08 Jul 2015 08:58:08 GMT

vPrevMonth should be as follows

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

Re: Set Analysis with Variable ( Previous month)

linusblomberg — Wed, 08 Jul 2015 09:17:36 GMT

Won't that give you yesterdays date?

Re: Set Analysis with Variable ( Previous month)

sasiparupudi1 — Wed, 08 Jul 2015 09:22:28 GMT

Yeah sorry, It should be

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

Re: Set Analysis with Variable ( Previous month)

Michiel_QV_Fan — Wed, 08 Jul 2015 09:29:42 GMT

Diane, that is exactly what this Month_ID solution does,

with the bonus that if you select a different month it will show the month before that month.