
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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
Accepted Solutions


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or maybe
=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},
[Fiscal Year Last Week] = {'$(=Maxstring([Fiscal Year Last Week]))'}
>}
[Record Count])


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
=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])
If a post helps to resolve your issue, please accept it as a Solution.


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Or maybe
=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},
[Fiscal Year Last Week] = {'$(=Maxstring([Fiscal Year Last Week]))'}
>}
[Record Count])


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
yes, but the field is created by concatenating so would it still retain the dual value?
If a post helps to resolve your issue, please accept it as a Solution.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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]


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
