topic Re: Need help with dynamic Year calculation in set analysis. in QlikView

Need help with dynamic Year calculation in set analysis.

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have to make the below expression dynamic in set analysis.

I tried Year = max(Year) & 'OL' which is not right; Then I tried assigning max(Year) & 'OL' to variable and passed it to the Year field in the expression, it says 'Allocated memory exceeded'

=sum({<Year = {'2016 OL'}>}Sales)

NOTE: I am using this in a bar chart and a straight table.

Can someone please help me with this ASAP.

Thanks!

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 15:24:43 GMT

Your year field includes Text? May be this:

Sum({<Year = {"$(=Max(Year) & ' OL')"}>}Sales)

Re: Need help with dynamic Year calculation in set analysis.

swuehl — Fri, 08 Apr 2016 15:31:12 GMT

How do these year values look like? more than 1 value per year, with different chars at the end?

Maybe like

Sum({<Year = {"$(=Maxstring(Year))"}>} Sales)

Re: Need help with dynamic Year calculation in set analysis.

Anonymous — Fri, 08 Apr 2016 15:37:42 GMT

This didn't work sunny;

Any other thoughts?

Re: Need help with dynamic Year calculation in set analysis.

Anonymous — Fri, 08 Apr 2016 15:38:24 GMT

MaxString didn't work

I have appended text OL to the year 2016, so the year looks like 2016 OL

please let me know if you can think of something else

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 15:38:49 GMT

Try Stefan solution and see if that works, if not please consider sharing a sample

Re: Need help with dynamic Year calculation in set analysis.

swuehl — Fri, 08 Apr 2016 15:40:13 GMT

If you use

Sum({<Year = {"$(=Maxstring(Year))"}>} Sales)

as expression in your straight table and leave the expression label empty, then hover with the mouse over the column header, what do you see?

Re: Need help with dynamic Year calculation in set analysis.

swuehl — Fri, 08 Apr 2016 15:41:38 GMT

And could you elaborate on 'does not work', please. Expression returns zero? Wrong value <> zero?

If you need to find the max year in your data ignoring selections:

Sum({<Year = {"$(=Maxstring({1} Year))"}>} Sales)

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 15:42:46 GMT

When you put =MaxString(Year) -> What do you see in a text box object? Do you see 2016 OL?

Re: Need help with dynamic Year calculation in set analysis.

Fri, 08 Apr 2016 15:51:15 GMT

Hi Stefan,

Just out of curiosity, would the two formulas produce the same result?

Sum({<Year = {"$(=Maxstring(Year))"}>} Sales)

and

Sum({<Year = {"$(=Maxstring({1} Year))"}>} Sales)

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 15:52:56 GMT

Without any selection they will produce the same result. With a selection, they may or may not produce a same result. Essentially, the second expression will always show the same result because it is restricted by using set analysis to now change based on selections ({1})

Re: Need help with dynamic Year calculation in set analysis.

Anonymous — Fri, 08 Apr 2016 15:55:01 GMT

=MaxString(Year) gives me 2016 OL

ANn Sum({<Year = {"$(=Maxstring({1} Year))"}>} Sales) worked!!

Thanks much you 2!!

Re: Need help with dynamic Year calculation in set analysis.

Fri, 08 Apr 2016 15:55:37 GMT

Thanks Sunny exactly the answer I was looking for.

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 15:57:12 GMT

Awesome, I am glad it did. Please close the thread by marking Stefan's answer as correct (in addition to marking it as helpful) and any other helpful answers

Re: Need help with dynamic Year calculation in set analysis.

swuehl — Fri, 08 Apr 2016 15:57:41 GMT

If max year is always today's year, this should also work

Sum({<Year = {'$(=Year(Today()) & ' OL')'}>} Sales)

Re: Need help with dynamic Year calculation in set analysis.

sunny_talwar — Fri, 08 Apr 2016 16:29:08 GMT

Thank you

I really appreciate you for taking your time to doing as I asked