topic Re: Year,month expression in QlikView

Year,month expression

narender123 — Wed, 22 May 2013 13:22:05 GMT

Hi All,

I have 2 filters

1) Year

2) Month

my expression(1):

=sum({<Year={$(=max(Year))},Month={$(=MaxString(Month))}>}Amount)

so by default it should show the amount of max month of max year.

but it not working

When i select year filter only then it shows the that year max month amount(means working fine)

expression

=sum({<Year={$(=max(Year))}>}Amount) is working fine.

the problem is that both together (month and year) not showing result bydefault for max(month ) of Max(year).

so anybody can tell me whats is wrong with my expression(1).

Thanks in advance.

Narender

Re: Year,month expression

Gysbert_Wassenaar — Wed, 22 May 2013 13:40:22 GMT

You probably need to use max instead of maxstring: Month={$(=Max(Month)). But if your month is a real string then you need to change the field or add another field in the script so you can use the month number. The alphabetic order of the month names isn't the right order to find the max month number.

Re: Year,month expression

Wed, 22 May 2013 13:43:04 GMT

Hi Narender,

Better you create one variable like

for year : vmaxyear = max(year)

month : vmaxmonth = month(max(date field))

now you call that variable in expression like

=sum({<Year={$(vmaxyear)},Month={$(vmaxmonth )}>}Amount)

hope it is working

Regards,

Nirmal.

Re: Year,month expression

Wed, 22 May 2013 13:57:02 GMT

If your Month field is a true string, then, like Gysbert said, it will not return the expected max every time. If you have the actual date field, use the Month() function to calculate the date, and it will be a Dual value (string and number). This might solve your issue.

Alternatively, you can create your own dual using Dual(String, Number).

Another solution... If Month is a string then you should qualify your set analysis for that field. Notice the double quotes added to the Month={"[some_value]"}. This will search for a match or use single quotes for an exact string match.

Sum({<Year={$(=max(Year))},Month={"$(=MaxString(Month))"}>} Amount)

Re: Year,month expression

Wed, 22 May 2013 13:59:54 GMT

Just thought of another option...

If you always plan to use YEAR and MONTH from a specific transaction date, then you can use MonthName() to create a Month-Year value (again a dual) which makes your set analysis simpler. You can create the field in your script and use the MONTH_YEAR={Max(MONTH_YEAR)} in your set analysis.

Re: Year,month expression

narender123 — Thu, 23 May 2013 06:33:32 GMT

Hi All,

Thank you all for your suggestion

I have tried all your expression but still its not giving result as per my question.

Narender

Re: Year,month expression

Thu, 23 May 2013 07:28:09 GMT

Hi,

Try following the steps below and see if helps,

1) Create a inline table with Monthname and Monthnumber

2) do a applymap or lookup for the month name in your original table. This will help you to create numbers for each month (1....12)

3) Use the same expression that you have created

Thanks,

Re: Year,month expression

er_mohit — Thu, 23 May 2013 07:37:21 GMT

Try this

=sum({<Month=,Year=,Year={"$(=max(Year))"},Month={"$(=num(Max(Month)))"}>}Amount)

Re: Year,month expression

narender123 — Thu, 23 May 2013 10:13:02 GMT

Thanks.

I have already created Monthno and connected it with my master calender .

Not working

Re: Year,month expression

narender123 — Thu, 23 May 2013 10:13:21 GMT

thanks.

But still not working

Re: Year,month expression

Thu, 23 May 2013 12:40:59 GMT

Can you provide us with an example QVW? I think we would be able to solve your problem faster with your specific example - with just the calendar and fake data for Amount.

If you try er.mohit's example above. I would remove the double quotes since you don't need to search for the number. Also, you don't need to ignore the selections and then redefine year and month.

Try this:

=Sum({<Month=,Year={$(=Max(Year))},Monthno={$(=Max(Monthno))}>} Amount)

See the attached example.