Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
bobbydave
Contributor 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

Tags (2)
1 Solution

Accepted Solutions
MVP
MVP

Re: Max First Sorted Value

Or maybe

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

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

      >}

     [Record Count])

5 Replies
vinieme12
Esteemed Contributor II

Re: Max First Sorted Value

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

MVP
MVP

Re: Max First Sorted Value

Or maybe

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

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

      >}

     [Record Count])

vinieme12
Esteemed Contributor II

Re: Max First Sorted Value

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

bobbydave
Contributor III

Re: Max First Sorted Value

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]

MVP
MVP

Re: Max First Sorted Value

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.