Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bobbydave
Creator III
Creator III

Max First Sorted Value

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

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Or maybe

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

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

      >}

     [Record Count])

View solution in original post

5 Replies
vinieme12
Champion III
Champion III

=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])

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
swuehl
MVP
MVP

Or maybe

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

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

      >}

     [Record Count])

vinieme12
Champion III
Champion III

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

Vineeth Pujari
If a post helps to resolve your issue, please accept it as a Solution.
bobbydave
Creator III
Creator III
Author

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

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.