Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Find maximum week number for the maximum year number

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)

1 Solution

Accepted Solutions
sunny_talwar

May be this:

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

View solution in original post

7 Replies
sunny_talwar

May be this:

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

Anonymous
Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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)

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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