Find maximum week number for the maximum year number

[#1]

Hello,

My first expression (below) for finding the year for which SHIPMENT_AVG_3_WEEKS is greater or equal to 0 works correctly in my application. Output is 2016

=max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}YR)

Next my second expression (below) for finding the maximum week of the year from above expression is not working.. Could you please help?

=max({1<SHIPMENT_AVG_3_WEEKS={">=0"}, OPE_YY={'=max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}YR)'}>}WEK)

[sunny_talwar · Accepted solution]

May be this:

=Max({1<SHIPMENT_AVG_3_WEEKS = {">=0"}, OPE_YY = {"$(=Max({1<SHIPMENT_AVG_3_WEEKS={'>=0'}>} YR))"}>} WEK)

[Anonymous]

what do you expect?

e.g. if you have a master calendar with 2015 and 2016, your Expression for year will get you 2016.

what week do you want to get

your Expression will bring you week number 53 (max week number Overall). do you want the max week in 2016 (which today would be 23). in this case you should try:

=max({<YR={'=$(=max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}YR))'},SHIPMENT_AVG_3_WEEKS={">=0"}, OPE_YY={'=max({<YR={'=$(=max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}YR))'} ,SHIPMENT_AVG_3_WEEKS={">=0"}>}YR)'}>}WEK)

[swuehl]

If you want to get an result per dimensional value, e.g. shipper or customer or product, this will not work since the DSE as well as the set analysis is only evaluated once.

If this is what you need, try

=FirstSortedValue( {1<SHIPMENT_AVG_3_WEEKS={">=0"} >} WEK, -DATE)

I've assumed you also have a DATE field in your master calendar.

edit: Instead of DATE, you could also use a YearWeek field or something similar.

[#5]

Thanks to all of you for your quick responses and for your help

I have tried Sunny's proposal as follows and it works great: It gives me the maximum week number for which SHIPMENT_AVG_3_WEEKS is >= 0 and for maximum year number which also has SHIPMENT_AVG_3_WEEKS >= 0

Sunny's answer:

=Max({1<SHIPMENT_AVG_3_WEEKS = {">=0"}, OPE_YY = {"$(=Max({1<SHIPMENT_AVG_3_WEEKS={'>=0'}>} OPE_YY))"}>} OPE_WEK)

[#6]

Hi again dears,

I'm building the result of above while loading the main script as follows by concatening the result of the max year with '-W' and concatening it with the result of the max week but im getting script error. Could you please help? :

LET v_ReportWeek = 'max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}OPE_YY)'

&

'-W'

&

'max({1<SHIPMENT_AVG_3_WEEKS = {">=0"}, OPE_YY = {"$(=Max({1<SHIPMENT_AVG_3_WEEKS={'>=0'}>} OPE_YY))"}>} OPE_WEK)' ;

I have also tried the following but again im getting script error:

LET v_ReportWeek = 'max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}OPE_YY)' 
&
'-W'
&
'max({1<SHIPMENT_AVG_3_WEEKS = {">=0"}, OPE_YY = {"$(=Max({1<SHIPMENT_AVG_3_WEEKS={'& chr(39) & '>=0'& chr(39) & '}>} OPE_YY))"}>} OPE_WEK)';

[swuehl]

QlikView will try to evaluate your LET statement in the script.

The dollar sign expansion

$(=Max({1<SHIPMENT_AVG_3_WEEKS={'>=0'}>} OPE_YY))

won't work in the script, it will return NULL.

I don't get an script error, but the variable will be incomplete, and also the concatenation will be happened to the literals, while I assume you want them happen to the aggregation results.

Try something like

LET v_ReportWeek = 'max({1<SHIPMENT_AVG_3_WEEKS={">=0"}>}OPE_YY)

&

''-W''

&

max({1<SHIPMENT_AVG_3_WEEKS = {">=0"}, OPE_YY = {"$' & '(=Max({1<SHIPMENT_AVG_3_WEEKS={''>=0''}>} OPE_YY))"}>} OPE_WEK)';

Note that I've replaced a single  single quote with two in the inner part of the statement.

[#8]

Yooohooo! Thanks dear swuehl i tried it and it works great!!!