Maxstring not Working

[Neha36]

Hi All,

Maxstring is not working in below scenario:

Suppose, we have year and week as 2022-W12, 2022-W11, 2022-W10, 2022-W9 and have used Maxstring(YearandWeek) but it is returning output as  2022-W9 instead of  2022-W12. Kindly help me on getting some solutions to fix this issue.

[Or]

2022-W9 is the correct result. W9 is a greater string than W10. You're looking at it as a number rather than a string.

You should probably use either Weekname() on a date field, or a Dual() field, which would allow you to get the maximum week based on its numeric (date) value.

[Neha36]

Hi,

Thank You for your suggestion. I tried both WeeKname and Dual but it is not working. The column value of column yearandweek is as 2022-W9, 2022-W10 etc from the calendar table.

WeeKname(yearandweek) or Dual(yearandweek) --> Is this correct? If yes, then its not working.

[Or]

That is not correct. These functions would need to be applied to the date field from which the week is being derived and they cannot be applied to a text string which is not a date. If no such field exists in your data, you would need to derive the numeric value of the week from the string value and create a dual field,which you could probably do by using something along the lines of:

Dual(YourString,num(KeepChar(YourString,'0123456789')))

This would result in the numeric values (202209, 202210,202211) being available for sorting or generating a max numeric value (or a FirstSortedValue() )

[Qlik-Gerardo]

@Neha36 Dual is a great solution ... but maybe you could go back one step and format Week as W09 (with a 0 before the 9)  instead of W9 ...

If you are using something like:

Year('2022-02-25') & '-W' & Week('2022-02-25')

>> OUTPUT >> 2022-W9

You can format the Week Number using this formula:

Year('2022-02-25') & '-W' & Num(Week('2022-02-25'),'00')

>> OUTPUT >> 2022-W09

You can replace the '2022-02-25' sample date for the name of your Date field, I'm using it just for testing and explanation purposes. This formatting will work as expected with MaxString.

---

If you need for some reason to stick to previous format (using W9), then you could use the similar suggested formatting as second parameter for Dual.

From Qlik Help
Dual() combines a number and a string into a single record, such that the number representation of the record can be used for sorting and calculation purposes, while the string value can be used for display purposes.

Syntax:  Dual(text, number)

So you need a text and a number version for the same value ...

In the first parameter you will use your current formula (the one with the desired output that combines text and number W9) and the second parameter will be used the number only version.

DUAL ( Year('2022-02-25') & '-W' & Week('2022-02-25') ,  Year('2022-02-25') & Num(Week('2022-02-25'),'00') ) as YearWeekDual

so you have inside Qlik something like this :

Dual ( '2022-W9', 202209) ... text version (1st parameter) and number version (2nd parameter)

Later when the field is used in graphics you can sort it by using NUMERIC value option, in that case Qlik will use the second value to do it.

Good luck!

Gerardo

[Neha36]

Thank You All.

I cannot format the year and week number as I am getting these values from calendar dimension table having column yearandweeknumber as 2022-W8, 2022-W9, 2022-W10, 2022-W11, 2022-W12 etc. 

Maxstring(yearandweeknumber) is stored in one variable and this variable is used in the existing code so how can this scenario can be resolved such that variable can be used in the QlikApp.

[Qlik-Gerardo · Accepted solution]

Hello,

I guess you could modify your expression to reformatting the content before MaxString by spliting every part and rejoining them like this :

MaxString( Left(yearandweeknumber,4) & '-W' & Num(Right(yearandweeknumber, Len(yearandweeknumber) - Index(yearandweeknumber,'W')),'00')

Explanation:

Left(yearandweeknumber,4) >> Split the year part

Index(yearandweeknumber,'W')) >> Search where is the W located

Right(yearandweeknumber, Len(yearandweeknumber) - Index(yearandweeknumber,'W')) >> Take all the numbers that follow W letter

Num( .... , '00') >> format the previous result and add a 0 before numbers with 1 digit

The final result before MaxString evaluation will be a number like 2022-W09, 2022-W10, 2022-W12

MaxString now will give you the desired result ... 

--

If you still need to reformatting the result ... to show it as 2022-W9 you could you Replace() function. 

Replacing W0 text for W text the final formula will be:

Replace(MaxString( Left(yearandweeknumber,4) & '-W' & Num(Right(yearandweeknumber, Len(yearandweeknumber) - Index(yearandweeknumber,'W')),'00'),'W0','W')

Take in consideration that the right formula depends of the final use for this formula, for sorting purposes the first one will work fine, for Label and Titles you can use the second one but this one will not work fine for sorting.

Regards,

Gerardo 

[Neha36]

Hi,

The below formula worked fine in my case:

MaxString( Left(yearandweeknumber,4) & '-W' & Num(Right(yearandweeknumber, Len(yearandweeknumber) - Index(yearandweeknumber,'W')),'00')

Thanks a lot for all your help and suggestion. 🙂