help with script

[#1]

Hi,

We have the following script built into our app which helps produce a useful chart:

[Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter,

However, we need to add in something that creates a new field called 'month-quarter'. That way we can have a monthly tracker overlayed on a quarter tracker on the same chart. We have tried the folowing expression directly after the lines above; however, it brings up the error saying "field 'month' not found".

(text(Month) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter,

Can anyone offer a solution throug an expression? - we want to avoid having to manually adding a column on the raw data which whilst works is not ideal.

Chris

[Anonymous]

the message is okay, as you create the field month in same script

you Need a preceding load

load *,

(text(Month) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter;

load

[Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter,

[MayilVahanan]

Hi

Try like this

Load *, (text(Month) & '/' & Quarter asMonthQuarter;

Load

[Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter

From datasource'

[sasiparupudi1]

try

(text(makedate(year([snapshot:]),month([snapshot:])) ) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter

[#5]

Hi Mayil,

I have put this script in but it now the first ( before text is in red. There is end bracket.

The error message says "error in expression ")" expected

[#6]

Hi Rudolf,

I have put this script in but now the first ( before text is in red. There is no end bracket.

The error message says "error in expression ")" expected

[senpradip007]

Try like

Load *, (text(Month)) & '/' & Quarter asMonthQuarter;

Load

      [Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter

From datasource'

[#8]

Hi Chris,

just remove the bracket and the preceding load should work for you

Load *, text(Month) & '/' & Quarter as MonthQuarter;

Load....

Joe

[Anonymous]

I copied your Expression. when I Count your brackets one bracket is to much.

so use

text(Month) & '/' & text('Q' & Ceil((Month([Date Survey Completed])-3)/ 3)) as MonthQuarter

(first bracket deleted)

[MayilVahanan]

Hi

Sorry, I didn't notice that

Load *, text(Month) & '/' & Quarter asMonthQuarter;

Load

[Date Survey Completed],
      makedate(year([snapshot:]),month([snapshot:])) as Month,
     YearName([snapshot:],0,4) as FinancialYear,
     QuarterName([snapshot:],0,4) as QuarterName,
     'Q' & Ceil((Month([snapshot:])-3)/ 3) as Quarter

From datasource;