Max First Sorted Value

[bobbydave]

I have an expression where I am using the previous Fiscal Week which I have created in my Calendar

  ApplyMap('MapDateToFiscalYear',Date)

      &'-'& if(  Month(Date) = 1 and  Year(Date) = 2016 and  week(Date) > 4, '01',

       // else

    right(WeekName(Date,0,-2),2)-1)        as [Fiscal Year Last Week]

which gives me my value in a list box.

Last Week     This Week

2017-30          2017-31

2017-29          2017-30

2017-28          2017-29

.

.

.

.

My expression below then will only ever use the Max fiscal week value during a fiscal month

=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

       [Fiscal Year Last Week] = {$(=Max(FirstSortedValue([Fiscal Year Last Week])))}

      >}

     [Record Count])

I am trying to put in the value of 2017-30 (not physically, I want Qlikview to  be intuitive and realise it needs to be the max value in the Last Week object.

Help appreciated

[vinieme12]

=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

       [Fiscal Year Last Week] = {$(=FirstSortedValue(AGGR([Fiscal Year Last Week],Date),-AGGR(Date,Date)))}

      >}

     [Record Count])

[swuehl · Accepted solution]

Or maybe

=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},

       [Fiscal Year Last Week] = {'$(=Maxstring([Fiscal Year Last Week]))'}

      >}

     [Record Count])

[vinieme12]

yes, but the field is created by concatenating so would it still retain the dual value?

[bobbydave]

One more question. Will max string work on 2 values?

[Fiscal Year Last Week] = {'>=$(=MaxString([Fiscal Year 6 Weeks Ago]))<=$(=Maxstring([Fiscal Year Last Week]))'}

I am trying to get the figure betwen 6 weeks ago and last weeks figure

I did the same with my calendar

ApplyMap('MapDateToFiscalYear',Date)

      &'-'& if(  Month(Date) = 1 and  Year(Date) = 2016 and  week(Date) > 4, '01',

       // else

    right(WeekName(Date,0,-2),2)-6)        as [Fiscal Year Last Week]

[swuehl]

Hm, not sure what you are exactly trying to do.

But take care that you are using the right field to modify. If you want to select last week's count, I assume you need to make the selection not on [Fiscal Year Last Week], but on something like This Week.

In other words, if the dollar sign expansion returns for example 2017-30, then

[Fiscal Year Last Week] = {'2017-30'}

would actually select week 2017-31, right?

So if you want to select week 2017-30, you probably need to use something like

[Fiscal Year Week] = {'2017-30'}

to select data of that week.

Besides this, I would suggest creating all calendar fields as duals, then you can also use a numeric range search like you are trying to do when using something like {'>=...<=...'}

I think the numeric range search won't work with pure text values.