Help required in dynamic chart title!

[#1]

Hi All,

This is the expression i use for displaying the chart title. What i want i instead of displaying the in numbers i want to show the month name in words. So i am not sure what changes has to be done with Max(Month) in my script to get the required result.

===Max(Month) & ',' &Max(Year) &' vs ' & (Max(Month)-1) & ',' &Max(Year)& ' Sales Growth @ ' & num(Sum({$<Year={$(=Max(Year))},Month={$(=Max(Month))}>} SalValue) / Sum({$<Year={$(=Max(Year))},Month={$(=Max(Month)-1)}>} SalValue), '#%')

By the way i have INLINE in my script to show the month name in words. It has been given below. Check whether anything can be done with this in the above expression.

MonthName:
load * inline [
Month, MonthName
4,Apr
5,May
6,Jun
7,Jul
8,Aug
9,Sep
10,Oct
11,Nov
12,Dec
1,Jan
2,Feb
3,Mar];

Thanks and Regards,

Rikab

[#2]

Hi Rikab!

This is what I did:

I first created a MonthName (Date) as MonthName that returns the month and year.

Then, use this: date(Max(MonthName)-1,'MMM')

Hope this will work.

Lester

[#3]

Hi Lester!

Thanks for your reply! Please let me know the following things!

I first created a MonthName (Date) as MonthName that returns the month and year. --> Where this has to be done. Please explain!

Then, use this: date(Max(MonthName)-1,'MMM') --> This has to be in my expression where and all i need month right?

Thanks and Regards,

Rikab

[#4]

Hi Rikab!

MonthName (Date) should be done on your script. If you have a Calendar table in your application, it is where you usually put it together with Year(Date) and Month(Date).

Yes! that will be placed in your expression label.

Regards,

Lester

[#5]

Hi Lester!

MonthName (Date) should be done on your script. If you have a Calendar table in your application, it is where you usually put it together with Year(Date) and Month(Date). --> I already tried this in my script in the past but doesn't seems to be working. Don't know the reason why! It throws the following error. Let me know whether you got succeeded using the same in your script while loading the data.

Yes! that will be placed in your expression label. --> Ok

Error:

[#6]

Hi Rikab!

MonthName is not a built in function in SQL, BUT it is a built-in function in QlikView script. After extracting data in SQL, add the MonthName function in your QlikView Edit Script.

Lester

[#7]

Hi Lester!

I didn't understood. Where do you want to add? Do i need to need to add in the edit expression like the below on or in the edit script? If it is there in the edit script let me know where to add.

This is the script which i am working on:

CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for Data=False;Tag with column collation when possible=False];

items:
SQL select i.c_account_code as account,i.c_item_code as itemcode,c_pack as pack,
i.c_mfac_code as mfaccode,rtrim(i.c_name)+' '+rtrim(i.c_pack) as itemname,
rtrim(i.c_account_code)+rtrim(i.c_mfac_code) as userid,rtrim(m.c_password) as password,
//rtrim(i.c_account_code)+rtrim(m.c_loginid) as userid,rtrim(m.c_password) as password,
i.n_mrp as MRP,i.n_newflag as recentitem,i.c_Scheme as schemes,m.c_name as mfacname,
c.c_name as contentname,c.c_note as contentname1
from item_mst i inner join mfac_mst m on i.c_mfac_code=m.c_mfac_code
left outer join content_mst c on i.c_content_code=c.c_content_code;
//where left(i.c_name,1)<>'~' and left(m.c_name,1)<>'~';
CONNECT TO [Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Password=mercury;Initial Catalog=VPDPL-1;Data Source=GL28;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Workstation ID=GL28;Use Encryption for Data=False;Tag with column collation when possible=False];

sales:
SQL select stk_sales.c_ac_code as account,
d_date as Date,Year(d_date) as Year,Month(d_date) as Month,stk_sales.c_item_code as itemcode,
n_purqty as PurQty,n_pur_Sch_qty as PurSch,n_purvalue as PurValue,
n_salqty as SalQty,n_sal_sch_qty as SalSch,n_salvalue as SalValue,
n_crqty as RtnQty,n_cr_sch_qty as RtnSch,n_crvalue as RtnValue,
n_clqty as StkQty,n_clvalue as StkValue from stk_sales,item_mst, mfac_mst
where stk_sales.c_item_code=item_mst.c_item_code and item_mst.c_mfac_code=mfac_mst.c_mfac_code;
//and left(item_mst.c_name,1)<>'~' and left(mfac_mst.c_name,1)<>'~';

Please let me know where exactly i need to add it. I am bit confused!

[Miguel_Angel_Baeyens]

Hello,

Having your month names in an inline table (you have them as well by default in the script, but anyway), what you are looking for will be something like

=Peek('MonthName', Max(Month) + 1, 'MonthName')

You have to order your inline table (1, Jan; 2, Feb; and so) in order this expression to work

[#9]

I used your script but it is not giving me any output. Hope you have understood my question. My question is instead of showing 12,2009 i want to show December, 2009 and instead of 11,2009 i want to show November, 2009 and both should happen dynamically.

I have used the below expression in the above chart. Let me know what changes has to be done.

====Max(Month) & ',' &Max(Year) &' vs ' & (Max(Month)-1) & ',' &Max(Year)
& ' Sales Growth @ ' & num(Sum({$<Year={$(=Max(Year))},Month={$(=Max(Month))}>} SalValue) /
Sum({$<Year={$(=Max(Year))},Month={$(=Max(Month)-1)}>} SalValue), '#%')

Thanks and Regards,

Rikab

[#10 · Accepted solution]

Replace

Max(Month) & ',' &Max(Year)

by
<pre>
date(makedate(Max(Year), Max(Month)), 'MMM YYYY')

The "MMM" in "MMM YYYY" will be repalced by the labels you have defined at the begining of your script :



You don't need other mapping.

The "YYYY" will be replaced by the 4 digits of the year .