Discussion Board for collaboration related to QlikView App Development.
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
Or maybe
=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},
[Fiscal Year Last Week] = {'$(=Maxstring([Fiscal Year Last Week]))'}
>}
[Record Count])
=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])
Or maybe
=Sum({< Country -= {'Korea', 'Latvia', 'Northern Ireland'}, [Control Description] -= {'Avaya'},
[Fiscal Year Last Week] = {'$(=Maxstring([Fiscal Year Last Week]))'}
>}
[Record Count])
yes, but the field is created by concatenating so would it still retain the dual 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]
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.