Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
May 18th 10AM ET, Live Chat, bring your QlikView questions. REGISTER
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 II
Champion II

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

vinieme12
Champion II
Champion II

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

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.