topic Re: MonthName in set anaylsis in QlikView

MonthName in set anaylsis

tschullo — Tue, 03 May 2016 15:43:02 GMT

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?

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 15:47:02 GMT

Can you try this:

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

Re: MonthName in set anaylsis

tschullo — Tue, 03 May 2016 15:49:37 GMT

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

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 15:51:08 GMT

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

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

Re: MonthName in set anaylsis

tschullo — Tue, 03 May 2016 16:02:45 GMT

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

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 16:03:58 GMT

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.

Re: MonthName in set anaylsis

tschullo — Tue, 03 May 2016 16:06:33 GMT

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

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 16:08:23 GMT

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)

Re: MonthName in set anaylsis

tschullo — Tue, 03 May 2016 16:12:11 GMT

That works. What do you mean by more dynamic?

Also, what if the two vals are different?

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 16:15:25 GMT

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.

Re: MonthName in set anaylsis

tschullo — Tue, 03 May 2016 16:20:32 GMT

Nice! Thanks a lot!

Re: MonthName in set anaylsis

sunny_talwar — Tue, 03 May 2016 16:22:39 GMT

Awesome

I am glad I was able to help. I would suggest marking any helpful answers (if you think there are any ) this way it can help people to spot some helpful answers in the future.

Best,

Sunny