topic Re: Expression Query in QlikView

Expression Query

dmac1971 — Tue, 27 Jan 2015 14:42:20 GMT

I have a table that has a monthly target in it using Mmm-YYYY for each month, ie Jan-2015, Feb-2015 etc. I then have a variable that sets the current month, vCurrentMonth, its set using :

=Month(Today()) & '-' & Year(Today())

and gives the value Jan-2015 for today. I then want this months budget to equal the Jan figure from the table and am using the following expression :

=Sum({<BUD_MONTH_YEAR={$(=($(vCurrMonthYear)))}>} BudgetValue)

But its not working, anyone any ideas? Thanks.

Re: Expression Query

sunny_talwar — Tue, 27 Jan 2015 14:50:27 GMT

Within the Master Calendar are you using the same method to calculate the Month Year? i.e.

MasterCalendar:

Load TempDate as Date,

Month(TempDate) & '-' & Year(TempDate) as BUD_MONTH_YEAR

Resident YourTable;

If you are using another method to calculate BUD_MONTH_YEAR in the script, then I would use the same method to calculate the vCurrMonthYear variable using the same functions.

Best,

Re: Expression Query

datanibbler — Tue, 27 Jan 2015 14:51:33 GMT

Hi Dermot,

1) You could use (easier perhaps) the DATE() function to get the year-month

(like >> DATE([date_field], 'MMM-YY') <<)

2) In the set_expression, I think it has to be

=Sum({<BUD_MONTH_YEAR={"$(vCurrMonthYear)"}>} BudgetValue)

Alternatively, I think you could also calculate that variable right there, in the set_expression.

HTH

Best regards,

DataNibbler

Re: Expression Query

alexandros17 — Tue, 27 Jan 2015 14:51:35 GMT

the correct syntax is:

=Sum({<BUD_MONTH_YEAR={$(vCurrMonthYear)}>} BudgetValue)

let me know

Re: Expression Query

its_anandrjs — Tue, 27 Jan 2015 14:52:09 GMT

Hi,

Why not if you use individual filter selection for Year and Month

Sum({<Year ={'$(=Year(Today()))'}, Month={'$(=Month(Today()))'}>} BudgetValue)

Regards

Anand

Re: Expression Query

dmac1971 — Tue, 27 Jan 2015 14:56:20 GMT

Have found this to work :

=Sum({<BUD_MONTH_YEAR={'$(vCurrMonthYear)'}>} BudgetValue)

Thanks.

Re: Expression Query

its_anandrjs — Tue, 27 Jan 2015 15:03:21 GMT

Hi, Dermot,

Another way is to change the variable to pure date format it seems you are using string dates as month year field

Let vCurrMonthYear = MonthName(MakeDate(Year(Today()), Month(Today())))

And in the Expression use

=Sum({<Monthname={"$(vCurrMonthYear)"}>} BudgetValue)

Ex:-

Regards

Anand