MonthName in set anaylsis

[tschullo]

I have the following script:

Load
MonthName(monYr) as MonYr,
myVal;

Load * Inline [
monYr, myVal
41760, 1
41791, 2
]

I then have a variable vMaxMonYr defined as:

=Max(MonYr)

then have a text box:

Script:

=$(vMaxMonYr) & '
MaxVal: ' & sum({<MonYr={"MonthName($(vMaxMonYr)"}>} myVal)

The result I get is:

41791

MaxVal=0

If I change the script to:

=$(vMaxMonYr) & '
MaxVal: ' & sum({<MonYr={'Jun 2014'}>} myVal)

it works ands I get:

41791

MaxVal=2

Why is that  and how can I write script so it works?

[sunny_talwar]

Can you try this:

=$(vMaxMonYr) & ' MaxVal: ' & FirstSortedValue(myVal, -MonYr)

[tschullo]

thanks Sunny, can you tell me why it won't work?

Your solution only works if I have a one to one with MonYr and myVal

[sunny_talwar]

I think the dollar sign expansion might have been missing, can you try this?

Sum({<MonYr={'$(=MonthName($(vMaxMonYr)))'}>} myVal)

[tschullo]

OK, so I thought the variable would work, but it turns out each customer in my data set can have a different max MonYr.

I need something like :

Sum({<MonYr={'$(=MonthName([max monYr for the given dimension iteration]'}>} myVal)

where dimension is CustName

[sunny_talwar]

That is why I proposed FirstSortedValue(myVal, -MonYr)

This can pick max MonYr for the dimension you are on. Set Analysis cannot because it is evaluated once per chart and not on each dimension.

[tschullo]

But then how do I get around multiple values for same monthYr?

[sunny_talwar · Accepted solution]

You can use DISTINCT if you wish to select one of them or there can be a more dynamic sort which can be used to pick the desired one.

FirstSortedValue(DISTINCT myVal, -MonYr)

[tschullo]

That works. What do you mean by more dynamic?

Also, what if the two vals are different?

[sunny_talwar]

Lets say you have two values are like this

Customer, MonthYear, Value

A, Mar-2015, 20

A, Mar-2015, 100

and you wish to pick 100 because it is the larger between the two, you can try this

FirstSortedValue(Value, -(MonthYear*10000 + Value))

By doing this, you would be adding more weight to the second row compared to the first row because of addition of 100 to the second row.