topic Re: Max First Sorted Value in QlikView

Max First Sorted Value

bobbydave — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Max First Sorted Value

vinieme12 — Tue, 05 Sep 2017 13:14:08 GMT

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

Re: Max First Sorted Value

swuehl — Tue, 05 Sep 2017 13:21:34 GMT

Or maybe

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

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

[Record Count])

Re: Max First Sorted Value

vinieme12 — Tue, 05 Sep 2017 13:26:53 GMT

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

Re: Max First Sorted Value

bobbydave — Tue, 05 Sep 2017 14:33:16 GMT

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]

Re: Max First Sorted Value

swuehl — Tue, 05 Sep 2017 15:33:59 GMT

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.