Year,month expression

[narender123]

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

[Gysbert_Wassenaar]

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.

[#3]

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.

[#4]

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)

[#5]

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.

[narender123]

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

[#7]

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,

[er_mohit]

Try this

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

[narender123]

Thanks.

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

Not working

[narender123]

thanks.

But still not working