topic Re: Use of maxstring() function in QlikView

Use of maxstring() function

mansi_dv — Tue, 25 Mar 2014 05:49:29 GMT

I want an expression like this.

=sum({<[Month] = {"April"}, Year = {"2012-13"}>}Data)

This works correctly but now I want month and year selected by the user from a list. I could have done it with GetFeildSelections() but I am using alternate States and GetFeildSelections() does not work with alternate states. So I used maxstring() function.

Having my state name as 'new', I did it as follows:

=sum({<[Month] = maxstring({[new]} [Month]),[Year] = maxstring({[new] [Year]})>}Data)

=sum({<[Month] = maxstring({[new]} [Month])>*<[Year] = maxstring({[new] [Year]})>}Data)

But its showing "error in set modifier element function name".

Then I tried creating variables for Month and Year as:

vMonth = maxstring({[new]} [Month]) and

vYear = maxstring({[new]} [Year])

still no correct output.

Anybody has a solution for this?

Re: Use of maxstring() function

Anonymous — Tue, 25 Mar 2014 05:53:27 GMT

Please post a sample app so that we can have a look to help you in a better way

Re: Use of maxstring() function

PrashantSangle — Tue, 25 Mar 2014 05:54:54 GMT

Hi,

Instead of using MaxString() You can use Only Max function

Like this,

=sum({<[Month] ={"=max([Month]"},[Year] ={"=max([Year])"}>}Data)

Regards,

Re: Use of maxstring() function

Tue, 25 Mar 2014 05:55:37 GMT

Hi Mansi,

Try this approach. Declare variables for your month and year.

e.g. vMonthnew =concat({new}Month)

vYearnew =concat({new} Year)

Now use the variable directly in the set analysis. That is more readable as well. You don't need maxstring.

Re: Use of maxstring() function

sushil353 — Tue, 25 Mar 2014 05:56:38 GMT

Hi,

First of all check your variable values using a text box.. see if the values are desired or not.

if all is well then you can try below expression:

=sum({<[Month] = $(vMonth ),[Year] = $(vYear )>}Data)

HTH

Sushil

Re: Use of maxstring() function

perumal_41 — Tue, 25 Mar 2014 06:48:23 GMT

Hi ,

Try below expression

=sum({<[Month] = {'$(=maxstring({[new]} [Month]))'},[Year] = {'$(=maxstring({[new]}[Year]))'}>}Data)

=sum({<[Month] = {'$(=maxstring({[new]} [Month]))'}>*<{'$(=maxstring({[new]}[Year]))'}>}Data)

Re: Use of maxstring() function

VishalWaghole — Tue, 25 Mar 2014 06:58:48 GMT

Hello Mansi,

No need to use maxstring here, You can just try with this

=Sum({<Month ={"$(MaxMonth)"},Year ={"$(MaxYear)"}>} Data)

Hope it will help you

- Regards,

Vishal Waghole

Re: Use of maxstring() function

VishalWaghole — Tue, 25 Mar 2014 07:04:38 GMT

Hello Mansi,

I forgate to tell you that, you have to declared two variable.

MaxMonth = Date(Max(Month(DateField)),'MMM')

MaxYear = Date(Max(Year(DateField)),'YYYY')

then use above expression.

Re: Use of maxstring() function

mansi_dv — Tue, 25 Mar 2014 07:04:57 GMT

I checked for my variables, they are working fine except that when nothing is selected - I get output as the last value in my listbox. What can be done to correct this?

And for the expression you gave, I am getting some output instead of the earlier one where i used to get zero but it is not the desired result.

Re: Use of maxstring() function

sushil353 — Tue, 25 Mar 2014 07:13:44 GMT

Hi Mansi,

Post your sample application for better resolution.

~Sushil

Re: Use of maxstring() function

mansi_dv — Tue, 25 Mar 2014 07:24:35 GMT

Thank you Perumal, your first expression worked correctly. I just have one more query. I am using listboxes to display different years and months. I have created a pivot table which changes its data based upon those selections.

But when I clear my selections, by default I have my pivot table loaded with the data of last values of the listboxes. What can be done for this??