Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

help with script

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

instead of preceding load just use

date(makedate(year([snapshot:]),month([snapshot:])),'MMM') & '/' & 'Q' & Ceil((Month([snapshot:])-3)/ 3) as MonthQuarter

Month might be a reserved word for Qlikview?

View solution in original post

19 Replies
Anonymous
Not applicable
Author

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'

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
sasiparupudi1
Master III
Master III

try

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

Not applicable
Author

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

Not applicable
Author

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
Specialist III
Specialist III

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'

Not applicable
Author

Hi Chris,

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


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

Load....


Joe

Anonymous
Not applicable
Author

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;

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.