topic Displaying the previous and current name in words in QlikView

Problem while using MonthName in the expression for calculation

Fri, 11 Dec 2009 18:33:41 GMT

Hi All,

I want the sales growth this month when compared to last month in gauge chart. Wants to the display the current month name in words instead of numbers in the chart.

What in need to show the month name in years. As i can't calculate the current month and previous name in the expression MonthName-1 as the values are in words. It can to be calculate using Month-1 and that name should be displayed in words. Please help me out!

Have attached my sample application with this.

Displaying the previous and current name in words

Fri, 11 Dec 2009 18:58:58 GMT

There is the system variable 'MonthNames' that holds an array of month names like this:

January;February,March....

you could use the 'index' function to match the month you have as a text to convert it to a number.

Regards,

Gordon

Displaying the previous and current name in words

Mon, 14 Dec 2009 17:07:03 GMT

Thanks for your reply. I am not able to understand your idea. Can you please create the same in my application which i have attached in my last post? user id: 0010012021 password:c2info99

Displaying the previous and current name in words

Sat, 19 Dec 2009 12:39:41 GMT

Hi Gordon,

I don't know how to do that. Can you please help me out. Still i couldn't solve this issue.

Displaying the previous and current name in words

Mon, 21 Dec 2009 14:35:35 GMT

Hi Gordon & All,

I have attached the application for your reference. In that date fields are in words. I wanted to show sales growth of current month when compared to last month. But the problem which i am facing here is. If the month name is in words in the list box what how can i use that in the expression when i want to compare the sales growth of last month when compared to this month. I can't use MonthName-1 to get the previous month like Month-1.

So please let me know the alternative for this.

To open the application please use useid:0010012021, password: c2info99

Displaying the previous and current name in words

Mon, 21 Dec 2009 17:13:50 GMT

This will give you the names of the 'Max(Month)' and 'Max(Month-1)' by giving a substring in the system variable MonthNames.

=subfield(MonthNames, ';', Max(Month)) & ' vs ' & subfield(MonthNames, ';', Max(Month)-1)

Is this what you wanted?

Regards,

Gordon

Displaying the previous and current name in words

Mon, 21 Dec 2009 17:17:32 GMT

This will give you the name of 'Max(Month)' and 'Max(Month -1) ' by substringing the system variable 'MonthNames' (use 'LongMonthNames' for the full month name)

=subfield(MonthNames, ';', Max(Month)) & ' vs ' & subfield(MonthNames, ';', Max(Month)-1)

Is this what you are after?

Regards,

Gordon

Displaying the previous and current name in words

Mon, 21 Dec 2009 18:05:08 GMT

Thankful to you if you do the required changes in the document that i have attached. Will be easy for me to learn.

Displaying the previous and current name in words

Mon, 21 Dec 2009 18:26:34 GMT

Hope now it will be easy for you to understand my problem. In the list list box month are displayed in words. And in the gauge group months are displayed in numbers. I want that to be in words instead of (12 vs 11). Problem here is if use Month as MonthName in the list box i couldn't should show the month in words as in the list box month is in words and couldn't do the MonthName-1 Calculation. hope you understood. If not please ask me where you couldn't understand.

Displaying the previous and current name in words

Mon, 21 Dec 2009 19:28:31 GMT

Hi,

Hope you might looking for like this:

Month(MakeDate(Year(Today()),MonthFiledName,Day(MonthStart(Today())))) Vs Month(MakeDate(Year(Today()),MonthFiledName-1,Day(MonthStart(Today()))))

I think create date using Number of month and then take Month name to use in the expression

while creating date using MakeDate Function use dummy Year and day

that will give you a Month name of selected Month and by subtracting 1 from Month field you will get Previos Month Name

Hope this approach will solve your problem...................

Or see the function Month and makeDate

e.g

Month(MakeDate(2009,12,21)) will give Dec

Displaying the previous and current name in words

Mon, 21 Dec 2009 19:48:11 GMT

Hi Rahul,

Thanks for your reply. Can you please do the same in my application. If you don't mind.

Thanks in advance!

Displaying the previous and current name in words

Mon, 21 Dec 2009 20:21:20 GMT

Hi ,

i was just changed your exp like this and this is working Properly

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

Displaying the previous and current name in words

Tue, 22 Dec 2009 11:51:54 GMT

Hi Rahul,

Thanks for your reply. Can you attachment the same document where you have made the changes. Attached document doesn't have any changes when compared to mine. It will be understandable if make the required changes in document and attach it.

Displaying the previous and current name in words

Tue, 22 Dec 2009 12:45:00 GMT

Hi,

Updated file is attached..

Displaying the previous and current name in words

Tue, 22 Dec 2009 13:02:43 GMT

Hi Rahul,

Thanks for your help. I am happy that it shows the month name in words. But when I make the selection in month it doesn't work. It shows "Too Few Expressions" message same as before. Let me know what to done now.

What i mean to say is that in the month list box month will be there in words only. Gauge graph should work when i select the the month from list box when it is in words.

Look into the attached list box. Here the month are displayed in words and the gauge graph doesn't seems be working when you make selection in that.

Help me out! Thanks in advance!

Displaying the previous and current name in words

Tue, 22 Dec 2009 20:04:32 GMT

Hi,

please find attached file and micro to understand ....

Displaying the previous and current name in words

Wed, 23 Dec 2009 13:10:48 GMT

Hi Rahul!
It's perfect now. Will be thankful to you if you can explain me about the script which you wrote in general tab. Max(Month) and Max(Month-1) is understandable but i am not able to understand rest of things especially Month(MakeDate(Year(Today()) and Day(MonthStart(Today())))) and how it works in between Max(Month) and Max(Month-1).

Also let me know you written this script in the general tab and there is no changes in the expression. Max(Month) and Max(Month-1) is not altered in the expression tab. Let where it is used and how it works now though the month name is in words and previously it was not working when the month name is in words.

Thanks in advance!

--------------------SCRIPT--------------------

=Month(MakeDate(Year(Today()),Max(Month),Day(MonthStart(Today())))) & ' vs ' & Month(MakeDate(Year(Today()),Max(Month)-1,Day(MonthStart(Today()))))

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

Displaying the previous and current name in words

Wed, 23 Dec 2009 19:46:20 GMT

Hi,

i just write a macro that you can see in the macro module (CTL + M).

that is activated on the monthname selection field in that i have selected respective month field value as set expression is depend on month field.

thats why it was working fine ..

to know more about macro please go through the API guides .

C:\Documents and Settings\ProgramFiles\Qlikview\documentation\API guides Application as it was installed by default

about :

Month(MakeDate(Year(Today()),Max(Month),Day(MonthStart(Today()))))

in this according to month selection i was created date by using MakeDate function and then take month of that date using Month Function ..

so we got month name in words.

this is one of the approach , instead you can us subfield function as suggested above that is more optimized than this functions..

Displaying the previous and current name in words

Thu, 24 Dec 2009 19:11:22 GMT

Hi Rahul!

Thanks for the explanation. But i will look into it after solving this problem which i am facing.

Your idea doesn't seems to be working. For more information please check with attached document.

If i select 4 in the Month and 2009 in the Year i am able to see the sales growth in the gauge. But if i select the Apr in the MonthName and 2009 in the Year i am not able to see the sales growth.

Why it is not showing the sales growth when i select the same month in the monthname like what i have selected in the month.

Hope you can give me solution for this.

Displaying the previous and current name in words

Thu, 24 Dec 2009 19:19:50 GMT

Hi,

Then i hope you have use the same field as you have given the expression for the list box.

For example : you might have given label name as Monthname and in expression you have put the conditions.

Now in the chart expression call this same label it will give you the correct data, else you can put the same expression in the gauge graph.

It has to show.

Thanks,

Mahasweta