Can't use this expression in expression field, only in text settings..

[#1]

Hi all!

I try to use this expression to get quarters in the format "Q2/2010":

'Q' & ceil(month(date(today())/3)) & '/' & year(date(today()))

This works perfectly in text objects and labels, but not in an if-filter in an expression like this:

if Quarter = 'Q' & ceil(month(date(today())/3)) & '/' & year(date(today()))

My graph is only saying that there is no data to display.

Any tip?

Thanks! 😃

[#2]

Have you tried this?

if Quarter = $(='Q' & ceil(month(date(today())/3)) & '/' & year(date(today())))

[brenner_martina]

Hi Nils,

your expression can look like this:

if(Quartal = Chr(39)&'Q' & ceil(month(today())/3) & '/' &

year(today())&Chr(39) , ... , ...)

You don't need the function date in for today()!

[#4]

Thank you both for your replies!

I can get neither of the suggestions to work.

Martinas suggestion wrote out '02/2010' (with the prime symbols) in a text object but did not work in my expression.

Couldn't get Nicks suggestion to work either on the text object nor in the expression.

Other thoughts about this challenge..?

[brenner_martina]

Can you upload a small example.qvw?

[#6]

I made a little example with source data from an excel-sheet. With Quarter hard coded as a data field in the sheet.

Actually this example graph works just fine with the expression I had in the beginning.

So, I thought the difference must be how I produce Quarter...? In the script I do it like this:

tbl_Timeperiod:
noconcatenate load CONTRY & IDNUMBER & DATE as ID,
Date(Date#(DATE,'YYYYMMDD'),'YYYY-MM-DD') as Date,
Month(Date#(DATE, 'YYYYMMDD')) & '/' & Year(Date#(DATE, 'YYYYMMDD')) as MonthName,
Year(Date#(DATE, 'YYYYMMDD')) as Year,
Week(Date#(DATE, 'YYYYMMDD')) & '/' & Year(Date#(DATE, 'YYYYMMDD')) as WeekName,
'Q' & ceil(Month(Date#(DATE, 'YYYYMMDD'))/3) & '/' & Year(Date#(DATE, 'YYYYMMDD')) as Quarter
FROM
randomfile.dat
(txt, codepage is 1252, embedded labels, delimiter is '\t', msq);

The DATE field has data in this format: 20100401

I the copied this script into my example project and used that instead of the excel quarter; got the same working result!!

Then I copied the working expression into my "real" project, and it worked!!

The solution was a mix of mine and Martinas. Martinas expression, without the chr(39), was correct. Mine expression, without the date(), was correct. I tried mine expression without date() in front of starting with my example project but I think I mixed up some parentheses..

Now it's working!! Thank you a LOT!